User FAQs

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

1) Is there any Microsoft documentation for VBA Programming ?

link - 

2) Is there a forum for questions about VBA Programming ?
There is 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 - 

There are also forums on answers for specific applications.

link - 
link -
link -
link -
link -
link -
link -

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

link - 

There are also other active forums.

link - 

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 - 

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

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext