User FAQs

If you have a question, please send it to us.
More questions can be found in the VBA > Macros section.

1) What is a Macro ?
A macro can be defined as lines of VBA code that can be run multiple times.
When you have a repetitive task you can use a macro to quickly repeat the steps.

2) Is there any Microsoft documentation for VBA Programming ?

link - docs.microsoft.com/en-gb/office/vba/api/overview/language-reference

3) Is there a forum for questions about VBA Programming ?
Post your questions in the Microsoft Q&A forum with the topic "office-vba-dev".

link - docs.microsoft.com/en-us/answers/topics/office-vba-dev.html

There is also a forum on techcommunity. The Excel hub forum with the label "Macros and VBA".
The following labels also exist but should not be used "Macros & VBA" and "VBA".

link - techcommunity.microsoft.com/t5/forums/filteredbylabelpage/board-id/ExcelGeneral/label-name/Macros%20and%20VBA

There is also a forum on Stack Overflow with the tag "vba".

link - stackoverflow.com/questions/tagged/vba

4) What is the Personal.xlsb ?
This is the personal macro workbook.
This is a special hidden workbook which you can use to store your macros.
It is open every time Excel is open assuming one has been created.

5) If a VBA macro is running and needs to be interrupted what keys would you press to interrupt it ?
(Escape)
(Ctrl + Break)
(Ctrl + Shift + Break)

6) If a VBA macro is running very slowly, what changes could you make to speed it up ?

Application.ScreenUpdating = False 
Application.EnableEvents = False 
Application.DisplayStatusBar = False 
Application.Calculation = xlCalculationManual 
ActiveSheet.DisplayPageBreaks = False 
Worksheet.EnableFormatConditionsCalculation = False

Read and write large blocks of cells in a single operation
Avoid selecting or activating any objects
Declare all variables with explicit data types
Never use the Variant data type

7) What is the difference between ThisWorkbook and ActiveWorkbook ?

Application.ActiveWorkbook     'workbook in the active window.  
Application.ThisWorkbook       'workbook where the code is actually running.

8) Write code to display the values in cells B1 to B5 in the Immediate Window ?

Dim lrowno As Long 
For lrowno = 1 To 5 
   Debug.Print Range("B" & lrowno).Value 
Next lrowno

9) What is the Application.Union method ?
Returns a range object that represents the union of two or more ranges.

Dim oRange1 As Excel.Range 
Set oRange1 = Application.Union(Range("A1:A5"), Range("C1:C5")) 
oRange1.Select

10) What is the Application.Intersect method ?
Returns a range that object that represents the rectangular intersection of two or more ranges.

Dim oRange2 As Excel.Range 
Set oRange2 = Application.Intersect(Range("B1:B5"), Range("A3:E3")) 
oRange2.Select

11) Write code to select all the non-blank cells

Application.Union(Cells.SpecialCells(xlCellTypeFormulas, 23), 
                  Cells.SpecialCells(xlCellTypeConstants, 23)).Select 
'the 23 = xlSpecialCellsValue.xlNumbers | xlTextValues | xlLogical | xlErrors

12) Can you give the name of 3 workbook level events ?

NewSheet - Occurs before an additional worksheet is added to this workbook. 
Open - Occurs before the workbook is opened. 
SheetCalculate - Occurs after the user re-calculates any worksheet in this workbook.

13) Can you give the name of 3 worksheet level events ?

BeforeDoubleClick - Occurs before any cell on the worksheet is double clicked. 
Change - Occurs when any cell on the worksheet has its value changed. 
SelectionChange - Occurs when the selection is changed on the worksheet.

14) Can you give the name of 3 application level events ?

WorkbookBeforeClose - Occurs before any open workbook is closed. 
WindowActivate - Occurs when the user moves focus to another window or open workbook. 
WorkbookModelChange - Occurs when a data model is updated.

15) Write code to test if a worksheet is a chart sheet ?

If (Sheets(1).Type = xlChart) Then 
If (VBA.TypeName(Sheets(1)) = "Chart Sheet") Then

16) Write code to ask a question and put the answer in cell A1 ?

Range("A1").Value = Application.InputBox("What is your name?")

17) What are Form Controls and how are they different from ActiveX controls ?
Form controls were added in Excel 5.0. ActiveX controls were added in Excel 97.
Form controls are very basic and can be placed on charts. ActiveX controls are more complicated and cannot be placed on charts.
Both types of controls can be placed on worksheets.
Both types can link to cells and ranges on a worksheet.

18) Remove any code that selects or activates ranges or objects.
You do not actually have to select or activate something in order to change or manipulate it.

Range("A1:D10").Select 
Sheets(2).Activate

Avoid using any of these in your macros. The current selection might not always be what you are expecting.

ActiveCell 
ActiveChart 
ActiveSheet 
ActiveWorkbook

19) Never use date literals

Dim dtMyDate As Date 
dtMyDate = #02/03/2021#

These dates ignore the regional settings and are always interpreted in US date format (mm/dd/yyyy)