Auto
Run
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 [5/1/2002]
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 [15/3/2002]
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"
Counting
Rows & Columns &
Sheets
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.
Sub Count()
myCount = Selection.Rows.Count 'Change Rows to Columns to count columns
MsgBox myCount
End Sub
The next macro counts the number of sheets instead. Refer to Protecting all sheets macro which uses this method.
Carriage Return [10/11/2002]Sub Count2()
myCount = Application.Sheets.Count
MsgBox myCount
End Sub
Sub TwoLines()
MsgBox "Line 1" & vbCrLf & "Line 2"
End Sub
Close All Files [23/3/2009]
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
Copying A Range [5/1/2002]
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 [17/2/2002]
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
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
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 Address
To get the current cell address (or
current range) to perhaps incorporate into your formula, you could use
the following code.
Sub MyAddress()
MsgBox ActiveCell.Address 'absolute cell reference with the pesky $ signs
MsgBox ActiveCell.Address(RowAbsolute:=False, columnAbsolute:=False) 'without the $ signs, you may choose to omit either one Row / Column absolute
End Sub
Current Cell Position [10/3/2002]
Sometimes we need to know the current
cell position.
This would
do the trick.
Sub MyPosition()
myRow = ActiveCell.Row
myCol = ActiveCell.Column
Msgbox myRow & "," & myCol
End Sub
Deleting
Empty
Rows
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.
Sub DelEmptyRow()
Rng = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To Rng
If ActiveCell.Value = "" Then 'You can replace "" with 0 to delete rows with 'the value zero
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
Application.ScreenUpdating = True
End Sub
The statement "Application.ScreenUpdating = False" prevents the screen from updating to ensure the macro runs faster and the screen will not flicker. Don't forget to set it back to "True".
Deleting Range
Names [15/03/2002]
To delete all the range names in your workbook, this
macro will do the trick.
Sub DeleteNames()
Dim NameX As Name
For Each NameX In Names
ActiveWorkbook.Names(NameX.Name).Delete
Next NameX
End Sub
Duplicates (Highlight
duplicates in Bold
Red) [27/10/01]
There are times you need to highlight duplicate data in your
worksheet. This
macro does the trick.
Sub DupsRed()
Application.ScreenUpdating = False
Rng = Selection.Rows.Count
For i = Rng To 1 Step -1
myCheck = ActiveCell
ActiveCell.Offset(1, 0).Select
For j = 1 To i
If ActiveCell = myCheck Then
Selection.Font.Bold = True
Selection.Font.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Select
Next j
ActiveCell.Offset(-i, 0).Select
Next i
Application.ScreenUpdating = True
End Sub
Emailing
Workbook [2/12/2001]
To email your current workbook the
following code.
Sub Email()
ActiveWorkbook.SendMail recipients:="julsn@yahoo.com"
End Sub
EDIT macros [30/6/2003]
Refer
to Text Manipulation.
Errors in macros [1/2/2002]
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
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
[8/2/2002]
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
Expiry Dates for Workbook / Macro
See
Security in Excel.
For,
Next
Loop [5/1/2002]
See Deleting
Empty Rows or Protect All
Sheets. A point to note is, try not
to use the For, Next loop because this method is very slow unless of
course you
don't know how to write your macro another way.
Flickering Screen
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 [31/1/2002]
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.
Public Function tax(income As Single)
Select Case income
Case
Is <= 2500
tax
= 0
Case
Is <= 5000
tax
= (income - 2500) * 0.05
Case
Else
tax
= (income - 5000) * 0.1 + 125
End
Select
End
Function
Goto
(a
range)
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.
Sub GoHere()
Application.Goto Reference:="Sales" OR Range("Sales").Select
End Sub
Going to the 1st Sheet
You can select the first sheet of the workbook without knowing the name
of the
sheet by referring to it by the index.
Sub FirstSheet()
Sheets(1).Select
End Sub
GoTo Sheet [18/03/2005]
Sometimes we have many sheets or sheets with long names & we
cannot
view them all. You can select which sheet to go to by using this macro.
Hiding
Sheets
To hide your worksheet from users you
can use the
following code.
Sub HideSheet()
Sheet1.Visible = xlSheetVeryHidden
End Sub
If you hide your sheets this way, users will not be able to unhide them using the menus. Only using VB codes will be able to display the sheets again.
Hiding Excel [3/9/2002]
You can hide the Excel application with this macro. This disables the
user from
using the excel menus. Don't forget to set it back to visible.
Sub HideExcel()
Application.Visible = False
End Sub
Input
Box
When you need to get input from users, you can use input
boxes.
This macro
will ask for the user's name and will display a message "Hello" plus
the user's name.
Sub GetInput()
Dim MyInput 'This line of code is optional
MyInput = InputBox("Enter your name")
MsgBox ("Hello ") & MyInput
End Sub
Inserting Rows
To insert rows required by a user is easy. Here the input box
is
used so
that a user can define the number of rows required.
Sub InsertRow()
Dim Rng
Rng = InputBox("Enter number of rows required.")
Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(Rng - 1, 0)).Select
Selection.EntireRow.Insert
End Sub
Here the macro uses the range technique where a range is first selected and then subsequently rows are inserted.
IF, Then Statement
See Protect All
Sheets
Joining
Text Together
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.