98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsHints and tips home » VBA hints and tips
VBA hints and tips
Use VbNullString instead of
When needing to default a String variable back to it's default of "" use vbNullString as in the following example:
Sub ClearText()
Dim strEmpName As String
strEmpName = "John Smith"
MsgBox strEmpName
strEmpName = vbNullString
MsgBox strEmpName
End Sub
Permalink (link directly to this tip). Added by Carlos, 8 Dec 2006
Stop Screen Flickering When Running Code
Running VBA code may cause the screen to flicker as the monitor is the slowest part of the program and cannot keep up with the very fast changes taking place.
To switch off the screen until the program is run enter the following code line:
Application.ScreenUpdating = False
The screen comes on automatically on completion of the program.
Permalink (link directly to this tip). Added by Carlos, 1 Dec 2006
Stuck in a Code Loop
If you ever get stuck in an infinite code loop when programing in VBA use
CTRL+BREAK
to exit the procedure
Permalink (link directly to this tip). Added by Carlos, 24 Nov 2006
Display Text In a Msgbox On Multiple Lines
When displaying text in a MsgBox you may need to show it over multiple lines. To do this use:
vbCrLf
As in:
MsgBox "The System has detected an error." & vbCrLf & "Contact your System Administrator"
The first sentence in quotes will appear above the second in the MsgBox.
Permalink (link directly to this tip). Added by Carlos, 24 Nov 2006
Empty The Clipboard with CutCopyMode
After each Copy/Paste operation in VBA, you should use the following line of code to empty the clipboard. This ensures that the computer memory doesn't overload:
ActiveSheet.Paste
Application.CutCopyMode = False
Permalink (link directly to this tip). Added by Carlos, 24 Nov 2006
Suspend DisplayAlerts in VBA
To stop Excel asking you things like "Do you want to delete this file...", use the following line of code at the beginning of the relevant VBA procedure:
Application.DisplayAlerts = False
At the end of the procedure make sure you use the following code to reactivate Display Alerts:
Application.DisplayAlerts = True
Permalink (link directly to this tip). Added by Carlos, 24 Nov 2006
Use GoTo to Select A Cell With VBA
To select a certain cell on a Worksheet you can use:
Application.Goto Reference:=Range("V300")
or more simply
Range("V300").Select
If, on the other hand, you want the selected cell to be the top/left cell on the screen you use:
Application.Goto Reference:=Range("V300"), Scroll=True
Permalink (link directly to this tip). Added by Carlos, 24 Nov 2006
Add A New Sheet at the end of the Workbook
To add a new sheet at the end of the workbook you need to count the sheets in the workbook using:
Sheets.Count
Then use this value as the rank of the sheet after which you want to add the new sheet:
Sheets.Add After:=Sheets(Sheets.Count)
Permalink (link directly to this tip). Added by Carlos, 24 Nov 2006
Count the Rows and Columns in a Selection
If you need to count the number of rows or columns in a worksheet use the following code:
Selection.Rows.Count - Returns the number of rows in the selection
Selection.Columns.Count - Returns the number of columns in the selection
Selection.CurrentRegion.Rows.Count - Returns the number of rows in the current region of the selection
Permalink (link directly to this tip). Added by Carlos, 24 Nov 2006
Good coding practice
It is important that you always comment the VBA code that you write. By doing this it will make the code much easier to read, as both yourself and other people will be able to see what the code is doing or meant to be doing. This is very important if there is more than one person who writes code in the company as you may have different styles of coding and may not really understand what the other person is trying to do. Also if you write some code and then do not touch it for a year or so you may actually forget what it is meant to be doing. so it is very important to include comments throughout your code and try and make sure that they are as useful as possible
Permalink (link directly to this tip). Added by David, 23 Nov 2006
Stop Display Alerts In Excel
When creating or deleting a Worksheet in Excel you don't want Excel to inform you "A file already exists....." Or "Do you want to save this file...".
To stop these alerts happening use the following line of code:
Application.DisplayAlerts = False
After the Create or Delete routine use:
Application.DisplayAlerts = True
Permalink (link directly to this tip). Added by Carlos, 7 Nov 2006