While it can be scary in trying to write your first code, it is also very fulfilling to see the result of your first code. I wrote my first code in QBASIC about 20 years ago, it was a simple
PRINT HELLO, WORLD.
When the computer did as I say and go on to show "HELLO, WORLD" on the b/w screen, I remember I had a big smile on my face. I couldn't remember what else happened but I know I was very very happy to see the result of my first code.
Here I'd like to share the joy with you on programming with Excel-VBA. If you are a total beginner, having no knowledge about programming, then this is for you! After writing your first code and understanding how it works, then I will go on and show you a few excel macro tricks that are commonly used.
First Code
You probably know that computer will do as you say, so where do we input the commands? For Excel (and other Ms Office products), it is through the VBA window which you can open it by pressing Alt+F11 in Excel. There is a more-obvious way to go to VBA window, but I always use the short-cut key anyway. By the way, VBA stands for Visual Basic for Applications, it is a light-weighted version of Visual Basic created specifically for Microsoft Office products.
And
where do we write in VBA window the commands? Generally, we write in a
Module. There is no module yet for a new workbook, so go ahead and right-click on the VBAProject, and add a module such as shown below
You should get to the screen shown below with the new module, namely Module1, automatically opened for you. If not, just double click on Module1 to open it.
It may looks very complicated what we have just done, especially with all the unfamiliar icons in the VBA window. But don't worry, you will get used to it.
It has been customary for all new programmers to command the computer to say
Hello, World! as their first code. There is a good reason why, because it is simple and good for initial understanding. So here's the codes for your first mini program in Excel-VBA
Sub MyFirstProgram()
MsgBox "Hello, World!"
End Sub
Go on and type these codes into the module, or just copy and paste if you like.
Now you can execute the code by pressing the play button.
See the message?
Congratulation!
That's it! you have written your first code!
OK Lets recap, to write a program,
- First, we go to VBA window, that is the window to give instructions to the computer.
- Then create a module, the notepad-like place where you write the commands (i.e the codes).
- Then hit play!
How simple is that!?!
Now that you have created your first program in Excel-VBA, I'll explain some technicalities so that you fully understand how the code works. Look at you first program:
Sub MyFirstProgram()
MsgBox "Hello, World!"
End Sub
- Your program codes should be sandwiched between the Sub and the End Sub lines. These two lines let the computer know where your program starts and ends. We call this the object-oriented paradigm, that is giving everything a sense of boundary. You will see that many other codes also adopted this paradigm.
- MyFirstProgram is the title of your program and is written immediately after Sub word. Note that only alphanumeric characters and underscores are allowed, no space, and title cannot start with a number. Always give a meaningful title for a program.
- The () is for passing information between programs when you have multiple programs that run in sequence. In other words, it is where input parameters are defined. For example,
Sub PromptAMessage(MyMessage As String)
MsgBox MyMessage
End Sub
That is pretty much it! Now you can go on and make your own program, be creative and be versatile.
Examples
Here are few small programs to help enrich your understanding.
How to declare a variable to store information for use later
Variables are useful for storing information to work with later on as the program runs. To declare a variable, we use Dim. There are a few data types of variable, here are 7 types you can learn as starters:
- Long - used to store whole numbers
- Double - used to store numbers with decimal places
- String - used to store text
- Boolean - used to store logical information like TRUE and FALSE
- Date - used to store date and time information. I repeat, its date and time information.
- Variant - used to store any type (all of the above) and also multi-dimensional information like a table
- Range - used to store information of cells location/addresses
Sub Example1()
'This example shows how to store a string of text.
Dim mytext As String
mytext = "Hello, World!"
MsgBox mytext
mytext = "The time is" & Now
MsgBox mytext
End Sub
Notes: the ampersand(&) is used to concatenate two strings together. Now is the code to return the current system date and time.
Sub Example2()
'This example shows how to store numbers.
Dim x As Decimal
Dim y As Decimal
x = 2.5
y = 6.6
MsgBox x + y
End Sub
Notes: If you store decimal numbers in a
long type variable, the decimals will be truncated leaving only the whole number. So define your variables correctly!
With a variable, you can also do iteration in macro.
Sub Example3()
'This example shows how to do iteration.
Dim x As long
x = 1
MsgBox x
x = x + 1
MsgBox x
End Sub
The first message will show 1 and the second message will show 2.
There are unending number of ways to play with variables, only time will show you the vast different methods.
How to ask for user input while your program is running
Sub Example4()
'This example shows how to ask for user input.
Dim NameStr As String
NameStr = InputBox("Please enter your name")
MsgBox "Hello " & NameStr
End Sub
Notes: If you declare wrong data type, for example if you declare NameStr As Double, you will get an error if you try to store text information into it.
How to change the value of a cell in current spreadsheet
Sub Example5()
'This example shows how to change the value of a cell
'in current sheet.
'There are 3 ways, but they are all the same.
Range("B5").Value = 55
Cells(6, 2).Value = 60 'This corresponds to cell B6
[B7].Value = 70
End Sub
How to change the value of a cell in specific spreadsheet
Often we have multiple sheets and we want to change the values across different sheets.
Sub Example6()
'This example shows how to change the value of a cell
'in specific sheet.
Worksheets("Sheet1").Range("B5").Value = 55
Worksheets("Sheet2").Range("B5").Value = 60
Worksheets("Sheet3").Range("B5").Value = 65
Worksheets("Sheet4").Range("B5").Value = 70
Worksheets("Sheet5").Range("B5").Value = 80
End Sub
Notes: Sheet1, Sheet2.... to Sheet 6 are the sheet names you used in your Excel. If you change them, then you have to change your code as well, unless you have a more dynamic way to identify the sheets.
How to loop through a few cells and change their values
We can ask the computer to do some actions repeatedly for a specified number of times (or continuously until a certain criteria is met).
Sub Example7()
'This example shows how to repeat an action
'for specified number of times (5 times for this example)
Dim i as long
For i = 1 to 3
MsgBox i
Next i
End Sub
Lets try to get a little creative on example 6
Sub Example8()
'An advancement of example 6
Dim i as long
For i = 1 to 5
Worksheets("Sheet" & i).Range("B5").Value = 50 + i * 5
Next i
End Sub
See how the codes shrink, imagine that if you need to do 1000 iterations!
Besides
For... Next, another way is use
Do... Loop. Below is an alternative looping method for example 7.
Sub Example9()
'An alternative to example 7
Dim i as long
i = 1
Do
MsgBox i
i = i + 1
Loop While i <= 3
End Sub
Notes: Loop While i <= 3 means that the Loop will repeat while i is less than or equal to 3. When i becomes greater than 3, the loop is ended.
Besides
Do... Loop While, we can also use
Do... Loop Until, which is literally the same but just change the condition for exiting the loop. Applying
Loop Until to example 9 will become
Sub Example10()
'An alternative to example 7 and 9
Dim i as long
i = 1
Do
MsgBox i
i = i + 1
Loop Until i > 3
End Sub
How to get the computer to make decision
Decision making is made using the IF function. You probably been very familiar with it in excel formula. Now you just need the correct syntax for using IF in VBA.
Sub Example11()
'Decision making example
Dim x As Long
x = 100
If x < 100 Then
MsgBox "x is less than 100"
ElseIf x = 100 Then
MsgBox "x is equal 100"
Else
MsgBox "x is more than 100"
End If
End Sub
Notes: You can put as many ElseIf as you like. This may be new concept to you from excel-formula as excel-formula don't support ElseIf.
Another example
Sub Example12()
'Decision making example
Dim t As Date
t = Now
If TimeValue(t) < TimeValue("12:00:00") Then
MsgBox "Good Morning"
Else
MsgBox "Good afternoon"
End If
End Sub
Notes: TimeValue is the function to extract the time information from a given date and time input.
How to copy and paste values
Lets say you want to copy values in cells B2:B10 to cells D5:D13, we can do this in two methods,
- simulation method
- direct method
Simulation method simulates the action of human, i.e. the action of copying (Ctrl+c) and the action of pasting (Ctrl+v). This method is often slower in performance but its advantage is everything gets copied, including formatting and formulas.
In direct method, it is much faster, but only values get copied. Copying formatting and formula is also possible with direct method but more difficult compared to simulation method.
Sub Example13()
'Copy and paste simulation method
Range("B2:B10").Copy Range("D5:D13")
End Sub
Sub Example14()
'Copy and paste direct method
Range("D5:D13").Value = Range("B2:B10").Value
End Sub
Designing your program
A real program will consist of combinations of codes used in the above examples as well as others not written here. Line-by-line the computer will execute your program according to the order you write the lines of codes.
Before you start writing a program, you should first have an overall aim for your program. Secondly break it down into actions necessary to achieve it, write it in English or any language you like. And finally, write each action in VBA language.
The second step is sometimes unnecessary. People already familiar with programming often just write codes out of thin air after having an aim in mind. Only when things get extremely complex, then we write it to help us visualise the program (this is called writing the pseudo codes).
How to store programs you have written
Consider keeping your DIY programs for use in other days so that you don't solve the same problem twice! There are few choice where to save your codes.
The program is used only in the current workbook
If the program can only be used for the workbook you just worked with, then write the program in the module of the workbook, then save the workbook in .xlsm extension.
The program can be used across many workbooks
If you have written a generic program which can be used repeatedly in many other workbooks, then you can store it in a different workbook, either in
- Macro-enabled workbook (.xlsm), or
- Binary workbook (.xlsb), or
- Add-in (.xlam)
The workbook that carries your generic program should be kept in an easy to find place. So it is a good idea to have a folder like My DIY Excel programs folder to basket up these workbooks.
Some examples of commonly used generic programs are:
- Highlight cells that has formula (a program we can toggle the highlight on/off)
- Convert text to number or vice versa
- Trim leading and lagging blank space off a selection of texts
- Rearrange comments besides their parent cells (comments often go hay-wire after sometime we use the worksheet. so we create a program that quickly rearrange them back!)
- Insert a barcode image from a given barcode number in a cell
- Crack a password protected sheet (Yes sometimes we need to as the previous owner left! Its no secret but do it ethically!)
Well done! You are now equipped with fundamentals of programming in VBA. You should now be able to write simple programs to do simple automation in Excel.
But...
Be sure to check back on my next post on How to comprehend all the codes in Excel VBA. There are thousands of thousands of codes in VBA, how do we comprehend all of them? This is an important question. When you understand how the codes are arranged in a grand hierarchy, you will be able to visualise a mind-map of the codes. If you have the mind-map, you don't have to memorise the codes, you don't even need to know if a particular code existed, but somehow you'll know where to find the right code you need on the go! That is the beauty of computer programming.
