Page History

1. Choose the "Developer" tab and open the VBA editor. If you don't locate the "Developer" tab, follow this guide. The following window will then open.
   
2. To write our first macro, choose "Instert" -> "Module".
   
   
3. First, we will make a Sub procedure that we may later add to our button. All Sub procedures have to begin with Sub and end with End Sub. A Sub can not return a value, and may be compared with a program in other programming languages, where as a function is an alternative. We then call the MsgBox function to create a new window displaying our message.
   
   
   

4. Lets now return to our Excel sheet to add the button. The insert button feature is shown in the image below.

   Info
   Tips: To align the size of the button with existing cells, hold down ALT while resizing. This applies to all sizings!

   
   

5. A new window will open. Choose the "Hello World" macro we just created and click "OK".
   
   
   
6. We may then rename our button by right-clicking on it and choosing "Edit text".
   
   
   
   
7. Finally, we can test our macro by clicking on the button. A window displaying the message "Hello World!" should then open.
   If it does, congratulations! You have just made your first macro using VBA.
   
   

Hello World video tutorial (no audio)

Arithmetic operators in VBA, written in the order of preference when evaluated:

Dim myResult

myResult = 2 ^ 3    ' Returns 8
myResult = 5 * 5    ' Returns 25
myResult = 10 / 3   ' Returns 3,333333333333333
myResult = 10 \ 3   ' Returns 3
myResult = 10 Mod 3 ' Returns 1
myResult = 6 + 5    ' Returns 11
myResult = -6 - 1   ' Returns -7

Comparison operators are used to compare expressions. They may return True, False or Null. Below is a table showing the basic comparison operators in VBA.

To learn when the compartion operators return Null, read the offical documentation on the comparison operators. Here, you will also find more detailed information regarding the actual comparisons done when using the operators. This includes outcome of comparing different variant types, such as a string and a double.

Let's look at some exampels:

Dim MyResult, Var1, Var2	' Variables are declared as type "Variant"
Dim str1 As String
Dim num1 As Integer

MyResult = (10 < 5)    		' Returns False.
MyResult = (1 <> 2)		   ' Returns True.
MyResult = ("5" > "4")    	' Returns True.

MyResult = ("hi" > "hello") ' Returns True, since ("i" > "e").
MyResult = ("hi" > "Hi")    ' Returns True, since the ASCII values for lowercase letters are higher than for uppercase letters.


Var1 = 5: Var2 = Empty
MyResult = (Var1 > Var2)    ' Returns True, since "Empty" is evaluated as 0.

Var1 = 5: Var2 = Empty
MyResult = (Var1 > Var2)    ' Returns False, since "Empty" is evaluated as 0.

Var1 = 0: Var2 = Empty
MyResult = (Var1 = Var2)    ' Returns True, since "Empty" is evaluated as 0.


Var1 = "1": Var2 = 4
MyResult = (Var1 > Var2)    ' Returns True, since a "Variant of type string" is always greater than a "Variant of type numeric".

str1 = "1": num1 = 4
MyResult = (str1 > num1)	   ' Returns False, since the "string" may be converted to a number, thus using a numerical comparison.

str1 = "a": num1 = 4
MyResult = (str1 > num1)	   ' Returns an error of type "Type missmatch", this because the "string" can not be converted to a number.

As we can see, you may compare almost anything in VBA. Knowing what the outcome of each comparison is, will give you a great advantage when coding.

In addition to the operators above, there are two more comparison operators, Like and Is. They have some special functionalities, and were therefore not described alongside the others.

The Is-operator is used to combine two reference variables. If both objects refer to the same object, the comparison returns True; if they do not, it returns False. An example is shown below:

Dim A As ThisWorkbook   ' A is declared as a valid object in order for "Is" to work.
Dim B, C, myResult

Set C = A           ' C refers to object A.
Set B = A           ' B also refers to object A.

myResult = C Is B   ' Returns True, since both variabels refer to the same object.

The Like-operator is used to compare strings. Its syntax is "result = string Like pattern", and it returns True if the string matches the pattern. If not, it returns False, and if either is Null, it returns Null.

Examples showing the use of Like:

Dim myResult
myResult = "I" Like "[A-Z]"     ' Returns True, since "I" is between "A" and "Z"
myResult = "i" Like "[A-Z]"     ' Returns False, since lowercase characters are places elsewhere in the ASCII-system
myResult = "a2a" Like "a#a"     ' Returns True, since the pattern requires any single digit between the a's.
myResult = "a5" Like "a #"      ' Returns False, since the pattern has a space between "a" and "#", which is not in the string
myResult = "ab" Like "a*b"      ' Returns True, since the * requires zero or more characters.
myResult = "a*b" Like "a[*]b"   ' Returns True, since the pattern requires a single "*" between "a" and "b".
myResult = "a**b" Like "a[*]b"  ' Returns False.
myResult = "aBBBa" Like "a*a"   ' Returns True.

Logical operators in VBA, written in the order of preference when evaluated:

The three last operators, Xor, Eqv and Imp may seem unfamiliar, as they are not included in other common languages, such Python or C++. To get a better understanding of them, including tables illustrating how the result is determined, read the operator documentation.

Dim myResult

' NOTE! This examples does not take into account if any expressions are "Null"

' For Not to evaluate to True, the expression has to be False.
myResult = Not False        ' Returns True, since True is not False.
myResult = Not 3 > 2        ' Returns False, since the expression after Not evaluates to True.

' For And to evaluate to True, both expressions has to be True.
myResult = True And False   ' Returns False, since one of the expressions are False.
myResult = 2 > 1 And 5 > 6  ' Returns False, since the last comparison is False.

' For Or to evaluate to True, one or both expressions has to be True.
myResult = True Or False    ' Returns True, since one of the expressions is True.
myResult = 3 < 2 Or 5 < 6   ' Returns False, since none of the expressions are True.

' For Xor to evaluate to True, only one of the expressions can be True.
myResult = True Xor True    ' Returns False, since both expressions are True.
myResult = True Xor False   ' Returns True, since only one expression is True.
myResult = 3 < 1 Xor 4 = 3  ' Returns False, since both expressions are False.

' For Eqv to evaluate to True, both expressions has to be the same.
myResult = True Eqv False   ' Returns False, since the expressions are different.
myResult = 2 = 4 Eqv 4 = 2  ' Returns True, since both expressions are False.
myResult = 3 = 3 Eqv 6 = 6  ' Returns True, since both expressions are True.

' For Imp to evaluate to False, expression 1 has to be True and expression 2 has to be False.
' Imp is not used a lot, so if you want to learn the operator, read the added documentation below this example.
' We will therefore not add any examples using Imp, as it would not be useful without the necessary background knowledge.

The implication operator Imp is considered the most advanced logical operator. In some situations, mastering its use may still be very useful. We suggest reading through this document by the Northern Illinois University to improve your understanding of implication in VBA: Implication in programming.pdf

Concatenation operators in VBA, written in the order of preference when evaluated:

+ can be used to add numerical values, as well as string concatenation. To learn more about the implications when adding variables of different types, read the operator documentation.

Dim myResult

myResult = "Concat" & "enation"     ' Returns "Concatenation".
myResult = "Concat" + "enation"     ' Returns "Concatenation".
myResult = 5 + 6        ' Returns 11.
myResult = 5 & 6        ' Returns 56.
myResult = "5" + 6      ' Returns 11.
myResult = "5" & 6      ' Returns 56.
myResult = "5" + "6"    ' Returns 56.
myResult = "5" & "6"    ' Returns 56.
myResult = 1 + "2" & "3"    ' Returns 33, since + are evaluated before &

Before we look at the different variable declarations, we need to establish the difference between the words explicit and implicit.

• explicit is defined as: "stated clearly and in detail, leaving no room for confusion or doubt."
• implicit is defined as: "implied, rather than expressly stated"
  

When coding in Visual Basic, you are by default not obligated to declare your variables before using them. This an example of an implicit variable declaration.

MyVariable = 10

When doing so, VBA will automatically declare it as a variant type, provided that the variable has not been declared before. This is a nice feature, but may cause trouble when writing larger scripts. Let's look at an example:

We have already written a lot of code using the variable "MyVariable". If we were to misspell "MyVariable" with "MyVaiable" once, VBA will think of it as a new variable, and declare it automatically, thus not informing us of our misspell.

MyVariable = 4
'We want to multiply MyVariable with 10
MyVaiable = MyVariable * 10

'... lots of code

'When we finally display MyVariable, thinking it equals 40
MsgBox MyVariable
'4 is displayed

To avoid this problem, we can use the Option Explicit Statement. It ensures that all variables must be explicitly declared by using the Dim, Private, Public, ReDim or Static statements. Otherwise, it will display an error at compile time.

If used, the "Option Explicit" statement must appear in a module before any procedures.

Option Explicit

Sub Example()

'Explicitly declaring MyVariable as an integer
Dim MyVariable As Integer

MyVariable = 4
'We want to multiply MyVariable with 10
MyVaiable = MyVariable * 10

'... lots of code

'When we finally display MyVariable, thinking it equals 40
MsgBox MyVariable
'4 is displayed

End Sub

Compiling the code above will result in the following error message.

If you want to make all your modules have the "Option Explicit" statement without having to remember to write it yourself, follow these instructions:

1. In your VBA editor, go to "Tools" → "Options".
   
   
   
2. Tick the "Require Variable Declaration" box. Click "OK".
   

myVar = Format(50000)
'myVar is now equal to the String "50000".

myVar = Format(50000, "Currency")
'myVar is now equal to the String "kr 50 000,00".

myVar = Format(50000, "#,##0.0")
'myVar is now equal to the String "50,000.0".

myVar = Format(0.88, "Percent")
'myVar is now equal to the String "88.00%".

myVar = Format(0.88, "0.0")
'myVar is now equal to the String "0.9".

myVar = Format("Ola Nordmann", ">")
'myVar is now equal to the String "OLA NORDMANN".

myVar = Format("Ola Nordmann", "<")
'myVar is now equal to the String "ola nordmann".

myVar = Format("123456789", "@@@-@@@-@@@")
'myVar is now equal to the String "123-456-789".