User FAQs

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

1) Is VBA an Interpreted or Compiled programming language ?
VBA does have a compilation step which converts the code into an intermediate language.
This intermediate language is then executed by an interpreter.
Even though there is a compilation step, VBA is considered to be an Interpreted language.

2) Is VBA a Loosely or Strongly Typed programming language ?
VBA is Strongly Typed, but with implicit conversions that make it feel loosely typed at times.

Dim x As Integer 
x = "123"   'The string is automaticall converted to a number  

3) Is VBA a Dynamically or Statically Typed programming language ?
VBA is a Dynamically Typed language because type checking happens at run time, not at compile time.
The Variant data type makes this dynamic behaviour more visible.
It is possible to declare static types meaning that the data type of a variable cannot change at runtime.
By giving all your variables specific data types and not using Variant, allows you to write code that is statically typed.

4) 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 

5) 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.

6) 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.

7) 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.

8) 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" 

9) 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 

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

Do 
   statements 
Loop While boolean-expression = True 

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

Do Until boolean-expression = False 
    statements 
Loop 

12) 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 

13) 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.

14) 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.