Excel Macros (VBA) and
tips for beginners. Simplified macros are used here for easy
understanding.

Last updated:
07-04-2010

Auto Run(back to top)Making your macros run automatically when opening your workbook. You
can either use the Auto Open method or the Workbook Open method. These
macros will display the message "Hello" when you open the workbook.

Sub Auto_Open()
Msgbox "Hello"
End Sub

This code would be located in
the module. However if you use the second method, the code must be in
the workbook (double click "This Workbook" in the explorer window).
Click on the drop down list (that says General) and select Workbook.
Click on the drop down list (that says declarations) and select Open.

Private Sub Workbook_Open()
Msgbox "Hello"
End Sub

Active Cell(back to top)An active cell is the current cell that is selected. This term is
used in many macros. This can be used as a marker. A good example is
when you need to move from your current cell.
Refer to Moving your cursor macro.

Adding Items to a
combobox (back to top)To add a combobox refer to User Form.
To populate a combobox or a listbox is the same. You could add from the
code or even from a range of cells in your spreadsheet. To add from the
code, just add this line to your code.

ComboBox1.AddItem
"Product A"
ComboBox1.AddItem "Product B"

AutoFilterTurn
On (back to top)
Use the following code to turn on an AutoFilter, if none exists

Sub TurnAutoFilterOn()

If Not ActiveSheet.AutoFilterMode Then 'check for filter, turn on if none exists

ActiveSheet.Range("A1").AutoFilter

End If

End Sub

AutoFilterTurn
Off (back to top)
Use the following code to turn off an AutoFilter, if none exists

Carriage Return (back to top)Sometimes you may want to put a line of text on the next row and not
let it continue on the first row. See this example in a message box.

Sub TwoLines()
MsgBox "Line 1" & vbCrLf & "Line 2"
End Sub

Close All Files [23/3/2009](back to top)Sometimes you may want to close all files without saving.
Doing it manually is a hassle with the question "Do you wanna save?"

Sub CloseAll()
Application.DisplayAlerts = False
myTotal = Workbooks.Count
For i = 1 To myTotal
ActiveWorkbook.Close
Next i
End Sub

Counting Rows &
Columns & Sheets(back to top)When you have selected a range, it is sometimes useful to know how
many rows or columns you have selected as this information can be used
in your macros (for eg when you have reached the end, you will know it
is time to stop the macros. This macro will do the trick.

Copying A Range
(back to top)Copy data from a specific range can be done with this
macro. Here data is copied from the current sheet to the activecell. (Refer
to Active Cell)

Sub CopyRange()
Range("A1:A3").Copy Destination:=ActiveCell
End Sub

To copy from a range in another
sheet (eg Sheet3) to the active cell you need to change the code to;

Sheets("sheet3").Range("A1:A3").Copy
Destination:=ActiveCell

Counter(back to top)To use a counter in your macro, just assign any cell to
retain the value. In this example the cell A1 is chosen. Each time the
macro is run, it adds the value 1 to the cell A1.

Sub Count()
mycount = Range("a1") + 1
Range("a1") = mycount
End Sub

Current Date (back to top)It's a good idea to insert the current date when you save
the file so that you can tell if it's the latest version. Of course this
is shown under file properties but how many people know where to find it?
You could also put the current date in the footer of your print out. It
is ideal if the date does not change unless the file is saved. You can
use this code. (On the drop down list that says declaration, select
before save and you will see the 1st line of code shown below - more
details refer to Auto Run macro.)

Private Sub Workbook_BeforeSave(ByVal
SaveAsUI As Boolean, Cancel As Boolean)
Range("A1") = Now 'Select any cell you
want
End Sub

Current Cell Content(back to top)Sometimes we need to know what the cell contains ie
dates, text or formulas before taking a course of action. In this
example a message box is displayed. Replace this with a macro should you
require another course of action.

Sub ContentChk()
If Application.IsText(ActiveCell) = True Then
MsgBox "Text" 'replace this line
with your macro
Else
If ActiveCell = "" Then
MsgBox "Blank cell" 'replace this line with
your macro
Else
End If
If ActiveCell.HasFormula Then
MsgBox "formula" 'replace this line
with your macro
Else
End If
If IsDate(ActiveCell.Value) = True Then
MsgBox "date" 'replace this line
with your macro
Else
End If
End If
End Sub

Current Cell Position (back to top)Sometimes we need to know the current cell position. This
would do the trick.

Deleting
Empty Rows(back to top)To delete empty rows in a selected range we can use the
following macro. The macro here uses the For Next Loop. First the macro
counts the rows in a selected range to determine the when the macro
should stop. The For Next statement acts as a counter.

Errors in macros(back to top)Ever had a macro running perfectly one day and the next
day errors keep on popping up even though you never made changes to that
macro? This is no fault of yours. Due to the excel VBA design, macro
files get badly fragmented due to heavy editing of macros, insertion of
modules & userforms. What you need to do is copy your macros else where,
delete the macros, save the file without macros. Open the file again and
import the macros and save it once more with the macros. You macros will
run properly until it gets fragmented again at a later stage.

Error Trapping(back to top)Trapping errors are important as users can do marvelous
things to mess up you macros. Here you can use either of these 2
statements.
- On
Error Resume Next OR
- On Error Goto ErrorTrap1
... more lines of code
ErrorTrap1:
... more code (what to do if there is an error)
The first statement will allow the macro to continue the next
line of code upon hitting an error but the second statement will run an
alternative code should there be an error.

Excel Functions(back to top)Using Excel functions in VBA is almost the same as using
them in a spreadsheet. For example to round an amount to 2 decimal
places in a spreadsheet would be;
=round(1.2345,2)
In VBA you would need to use the term Application followed by the
function ie;
ActiveCell =
Application.round(ActiveCell, 2)
For more examples see Rounding Numbers.

Find
the next available row(back to top)The code is not restricted to any
specific column and will find the true last row with data in a
spreadsheet and then selects the cell in column A for which the entire
row is empty.

Flickering Screen(back to top)Sometimes when you run a macro, the screen flickers a lot
due to the screen updating itself. This slows the macro done especially
when the macro has a lot of work to do. You need to include the
statement as shown below.
Also see
Deleting Empty Rows

Application.ScreenUpdating = False

You need to set the screen updating
back to true at the end of the macro.

Functions(back to top)
Creating function is useful as complicated formulas can be made easier
in code than in a spread sheet. Formulas can be protected so that users
cannot see or modify them. The example I use will calculate tax using
the Select Case Statement. Here's the scenario.
First $2500 is tax free.
Next $2500 is taxable at 5%.
Anything above $5000 is taxable at 10%.
In cell A1 type Income and in cell B1 type in your income in numbers say
$20000.
In cell A2 type Tax payable and in cell B2 type =tax(B1).
Put the following code in a module. The tax payable here would be $1625.

Goto (a
range)(back to top)
To specify a macro to go to a specific range you can use the Goto method.
Here I have already named a range in my worksheet called "Sales". You
may also use an alternative method ie the Range select method. Naming a
range in excel is recommended rather than specifying an absolute cell
reference.

Joining Text
Together (back to top)There are times where we import text file into Excel an we get text
that are separated. I received an email asking how put these text
together. Select across your cells first and run this macro.

Sub LowerCase()
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = LCase(cell)
End If
Next
End Sub

Message
Box (back to top)When you need to communicate with users, you can use message boxes.
This macro will display a message "This macro is created by Mark". The
Message Box appearance can be customised to show whether it is
Information, Critical Messages. Here the icon in the message box would
be different. The buttons can also be customise to show extra Yes, No,
Ok buttons. (Refer to vbYesNo macro). This macro will show you 3 different styles.

Sub MyMessage()
MsgBox "This macro is created by Mark"
MsgBox "The icon is different", vbInformation
MsgBox "The top title is different", vbExclamation, "Mark's Tips"
End Sub

Modeless Forms (back to top)Sometimes you want to allow users to be able to switch between your
form and your spreadsheet by clicking on either one. All you need to do
is set the form property of Show Modal to False or you can try this.
However this is only for Excel 2000 & above.

Sub myForm()
UserForm.show vbModeless
End Sub

Moving your cursor (back to top)Sometimes you need to move your cursor around your worksheet to
re-position it before running the next step of a macro. The movement
here uses the row, column position method.

Protecting all sheets
(back to top)To protect all the sheets this macro uses all the methods
contained in this page (see counting sheets). The If, Then statement
is also used here. This tests for a condition and if the condition is
TRUE, then the macro continuous the next line of code. In this case it
will END the macro. If the condition is NOT TRUE, then it will go to the
following line which in this case is to select the next sheet. You will
also notice the For, Next statement is also used. This acts as a
counter to tell the macro how many loops to run. In this case if there
are 3 sheets, the macro will run 3 times protecting all the 3 sheets.

Sub protectAll()
Dim myCount 'This line of code is
optional
Dim i 'This line of code
is optional
myCount = Application.Sheets.Count
Sheets(1).Select 'This line of code selects
the 1st sheet
For i = 1 To myCount
ActiveSheet.Protect
If i = myCount Then
End
End If
ActiveSheet.Next.Select
Next i
End Sub

Protecting your VB
code (back to top)To protect your VB code from being seen by others, all
you need to do is go to the project explorer, point at your project and
right click on it. Select VBA project properties, click on the
protection tab and check the Lock project for viewing and key your
password. That's it.

Random
numbers(back to top)For macros to generate random numbers, the code is takes this format
- Int ((upperbound - lowerbound +1) * Rnd +
lowerbound). Where the Upperbound is the largest number random
number to be generated and Lowerbound is the lowest.

Resizing a Range(back to top)Resizing a range is simple. You can apply this to
inserting rows & columns or to expand a selected range. This macro
resizes the range to 7 rows by 7 columns.

Sub ResizeRng()
Selection.Resize(7,7).Select
End Sub

Rounding Numbers(back to top)Here I will show how to perform different types of
rounding. Key in 12345 in any active cell and run the following code.

Sub Round()
ActiveCell = Application.round(ActiveCell, -3)
End Sub

This code round to the nearest 1000 thus giving
the value 12000.

ActiveCell =
Application.Ceiling(ActiveCell, 1000)
Replace with this line of code and it will round up to the
next 1000 ie 13000
ActiveCell = Application.Floor(ActiveCell, 1000)
Replace with this line of code and it will round down to the
next 1000 ie 12000

Running A Sub Routine(back to top)To run another macro from within a macro you need to use
the Call statement.

Sub Macro1()
Msgbox("This is Macro1")
Call Macro2 'This calls for Macro2 to run
End Sub

Save Date[15-08-2005] (back to top)There are times you may want to
change the date in a cell while saving your file.
Add this code to "thisworkbook"

Saving a file [23/3/2009] (back to top)There are times you may want a macro to save a file
automatically after running a macro. The second macro will save the file
with a name called "MyFile". You may specify the path if you need to.
The last macro saves all opened workbooks.

Security in Excel(back to top) Level 1 - To protect your excel files, there are a few steps
required to make it more difficult for other users to by pass security.
To prevent changes made to the worksheet, you need to protect your
worksheet. See protecting sheets. To prevent sheets from being renamed,
moved or deleted, protect the workbook. However protection of worksheets
and workbook can easily be hacked using macros as shown by an Excel
developer. I believe the next level of protection is protecting your
macros. To protect your macros, point at your project in the explorer
window, right click on it and select VBA project properties, click on
the Protection tab, check on Lock Project for Viewing and next key in
your password and you're done. Now the project cannot be viewed or
amended.

Level 2 - The next step is to force the user to enable your macro when opening your file. The best way is to
use a macro to hide the important sheets (see Hiding sheets) when saving
your file. Upon opening the file, a macro will be used to unhide these
sheets. If the user disables the macros when opening the worksheet, they
will not be able to view your worksheet unless they allow the macro to
run.

Level 3 - The final step is to put an expiry date for your
worksheet or your macro. However this has a draw back as the user may
change the system date of the computer to by pass the step.
Alternatively you can use a counter (Refer
Counter Macro)to allow a fixed number of access to your
worksheet or macro. Here you need to save the counter value each time
the file or macro is used. Upon reaching the defined limit, disable the
macro or disable the access of your worksheet.

The steps mentioned above are not 100% fool proof. But it will keep
normal users out but not hackers and crackers. Here I will not supply
the code as this can be lengthy and may be difficult to understand but I
believe these steps may be useful to some of you out there.

Select Case Statement(back to top)
This is a useful statement to use when you have many conditions. Too
many IFs in your code will only make you more confuse. See Functions macro.

Sub UpperCase()
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = UCase(cell)
End If
Next
End Sub

User Forms(back to top)
Adding user forms in your macro is simple. With user forms you can
create GUIs (Graphical User Interface) for user who do not have much
excel knowledge and make you excel programs more professional looking.
Go to your Visual Basic Editor window & click on Insert, select user
form and a user for will appear along with the toolbox. Now you can add
labels, buttons, text boxes and many more items. The property window
will allow you to customise your user form. To display your user form
use this code.

UserForm1.show
'to close with a macroUnload Me
'to close the form with a macro

vbYesNo(back to top)
There are times you may want users to click Yes or No. Just insert this
line of code. Here the Select Case statement is used.