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/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 

3) Do I need to make any changes to my code for it to run in 64-bit Office ?
Most of the code will work just fine, although there are a few things to look out for.

link - learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/64-bit-visual-basic-for-applications-overview 

4) How long should my subroutines and functions be ?
Never write procedures and functions that are longer than a full screen as these are hard to understand.

5) Do I have to put everything on one line ?
Use the Line Continuation Character to make your code more readable and to reduce the amount of horizontal scrolling.

6) Do I need to declare my variables before I use them ?
Always have Option Explicit at the top of your code modules to enforce variable declaration.

7) Should I prefix my variable names to indicate their data type ?
Always prefix your variables so you can quickly identify their data type.
Always prefix your variables with a data type indicator followed by a descriptive name.

8) Where should I declare my variables ?
Always declare your variables at the top of the subroutine or function, not just before you use it.
Never use Global variables unless absolutely necessary.

9) What is the Call Statement and when would it be used ?
The Call statement can be used when passing execution to a subroutine.
When it is used you must enclose any arguments in parentheses.
You should always use the Call keyword because it makes your code easier to read.

Sub MySubroutine() 
   MySub1 
   MySub2 50 
   Call MySub1 
   Call MySub2(50) 
End Sub 

Sub MySub1() 
End Sub 
Sub MySub2(ByVal iNumber As Integer) 
End Sub 

10) What is the difference between a Sub and a Function ?
A Sub (subroutine or procedure) is a method that does not return a value.
A Function is a method that ALWAYS returns a value.
If a Function has no 'As' keyword then the default is 'As Variant'
The following two functions are identical.

Function MyFunction() 
   MyFunction = 100 
End Function 
Function MyFunction() As Variant 
   MyFunction = 100 
End Function 

11) Can you use the Call statement when passing execution to a Function ?
Yes. You can use the Call keyword when you want to ignore the returned value.
In the first two function calls the value returned from these functions is assigned to the variable 'myResult'.
In the last four function calls the value returned from these functions is ignored.

Sub MySubroutine() 
   Dim myResult as Integer 

   myResult = MyFun1 
   myResult = MyFun2(50) 
   MyFun1 
   MyFun2 50 
   Call MyFun1 
   Call MyFun2(50) 
End Sub 

Function MyFun1() As Integer 
   MyFun1 = 100 
End Function 
Function MyFun2(ByVal iNumber As Integer) As Integer 
   MyFun2 = 100 + iNumber 
End Function 

12) Is it possible to return more than one value from a Function or Subroutine ?
Yes. There are several ways this can be achieved:
*) By returning an array from a function.
*) By returning a collection from a function.
*) By returning a class from a function.

Public Function MyFunction() As String() 
Public Function MyFunction() As VBA.Collection 
Public Function MyFunction() As MyClass 

*) Passing in several arguments to a subroutine using ByRef.

Public Sub MySubroutine(ByRef sArgument1 As String, _ 
                        ByRef sArgument2 As String) 

*) Passing in an array, which can only be passed by reference.
*) Passing in a collection, which is always passed by reference.
*) Passing in a class, which is always passed by reference.

Public Sub MySubroutine(ByRef vaArray() As String) 
Public Sub MySubroutine(ByRef myCollection As VBA.Collection) 
Public Sub MySubroutine(ByRef myClassVariable As MyClass) 

13) What is the difference between ByVal and ByRef and which is the default ?
ByRef - (default) - passes a pointer to the variable in memory.
ByVal - passes a copy of the variable.
Passing ByRef is the default when the passing mechanism is not specified.
The following two subroutines are identical.

Sub MySubroutine(iNumber As Integer) 
End Sub 
Sub MySubroutine(ByRef iNumber As Integer) 
End Sub 

14) File Name Conventions ?
Do not use full stops in your filenames, always use an underscore instead.