User FAQs

1) Would you need to make any changes to your code if you were running 64-bit Office ?
By default, Office 2010, 2013 and 2016 install the 32-bit version. You must explicitly choose to install the 64-bit version during setup. Starting with Office 2019 and Microsoft 365, the default is the 64-bit version.

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

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

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

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

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

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

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

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

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

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

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

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