User FAQs

If you have a question, please send it to us.

1) Is there any Microsoft documentation for VBA Programming ?

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

2) 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 - learn.microsoft.com/en-us/answers/tags/ 

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 

3) Should I indent my code with spaces or tabs ?
Always use tabs to indent your code to bring structure, never use spaces.

link - youtube.com/watch?v=SsoOG6ZeyUI 

4) Do I really need to add comments ?
Try and put your comments on the same line, on the right hand side of the code.
And only add "value added" comments which explain why, do not add trivial comments.
Make the names of your subroutines, functions and variables self-describing.
Always add a Comment Block above every procedure and function.

5) What is Option Explicit and what does it do ?

Option Explicit 

Including this at the top of a module means that all variables must be explicitly declared using the Dim statement.
This statement only affects the module it is declared in.

6) What problems do you have if you don't use Option Explicit ?
Variables do not have to be declared before they are used.
Mistyping a variable name will create a new variable and will probably create a run-time error.

7) What is the difference between a Literal Constant and a Symbolic Constant ?
A literal constant is a specific value such as a number, date or text.

Dim myDate As Date 
myDate = #12/31/2020# 

A symbolic constant is a literal constant that is represented by a name.

Public Const sVALUE As String = "text" 

8) What is the difference between a Do-While loop and a Do-Until loop ?
In a Do-While loop the condition must be True.

Do 
   statements 
Loop While boolean-expression = True 

In a Do-Until loop the condition must be False.

Do Until boolean-expression = False 
    statements 
Loop 

9) Write a Do-While loop that is executed at least once ?

Do 
   statements 
Loop While boolean-expression = True 

10) Write a Do-Until loop that has the condition at the top ?

Do Until boolean-expression = False 
    statements 
Loop 

11) Can you explain why the last subroutine call does not increment the amount variable ?
There are only two ways to call a subroutine.
The first is to not use the Call method in which case the arguments do not appear in parentheses.
The second is to use the Call method in which case the arguments have to appear in parentheses.
In the last subroutine call the argument (or expression) is being evaluated before being passed to the mySub2 subroutine.
You can enclose expressions inside there own set of parentheses to force them to be evaluated first.

Sub MySubroutine() 
   Dim iAmount As Integer 
   iAmount = 50 
   Debug.Print iAmount  '50  

   MySub2 iAmount 
   Debug.Print iAmount  '100  

   Call MySub2(iAmount) 
   Debug.Print iAmount  '150  

   MySub2 (iAmount) 
   Debug.Print iAmount  '150  
End Sub 

Sub MySub2(ByRef iArgAmount As Integer) 
   iArgAmount = iArgAmount + 50 
End Sub 

12) What is displayed in the Immediate Window ?

Sub MySubroutine() 
    Debug.Print VBA.TypeName(MyFunction1)   'displays Empty  
    Debug.Print VBA.TypeName(MyFunction2)   'displays Empty  
    Debug.Print VBA.TypeName(MyFunction3)   'displays Empty  
End Sub 

Function MyFunction1() 
End Function 

Function MyFunction2() As Variant 
End Function 

Function MyFunction3() As Variant 
Dim myVariant As Variant 
   MyFunction3 = myVariant    'defaults to Empty  
End Function 

A Function always has a value returned from it.
If the data type is not explicitly provided then a Variant data type will be used.

13) What is displayed in the Immediate Window ?

Sub MySubroutine() 
   Debug.Print MyFunction1   'displays 100  
   Debug.Print MyFunction2   'displays 0  

   Dim myObject As Object 
   Set myObject = MyFunction3 
   Debug.Print (myObject Is Nothing)   'displays True  
End Sub 

Function MyFunction1() As Long 
   MyFunction1 = 100 
End Function 

Function MyFunction2() As Long 
   Exit Function 
   MyFunction2 = 100 
End Function 

Function MyFunction3() As Object 
End Function 

The first function returns 100 because this is the value that is assigned to the function name.
The second function returns 0 because if a value has not been assigned to the function name (before an Exit Function or End Function) the default value for the return data type is passed back.
The third function returns Nothing because when a value is not assigned to the function name the default value for the return data type is passed back. In this case it is Object whose default value is Nothing.
When a function returns a reference data type it must be assigned to a variable using the Set statement.