If you run a Macro, the action can not be undone. Using the macros provided here is at your own risk!
Keep in mind that Macros can be very useful, but also very dangerous if coming from an unknown source.
The codes for these basic macros came from all over the internet or have been found by myself. Since these are very common and basic, I did not bother mentioning sources. Should someone recognise work of his own here and needs a name to be mentioned or the code removed, please contact me.
The macros shown here are my own selection and should do to get started or help you out on basic projects. Use your imagination to adjust them to fit in your project, or search for more info on the internet.
The codes here have been tried and verified to work on my Excel 2007 Version. Please note that it is often possible to reach the same goal in a different way.
Macros usually start with the line: "Sub Name()", where Name can be replaced by any name you assign to it. Macros end with the line "End Sub".
For clarity and ease of use testing different ones, I left these lines out unless they differ for some reason.
If in the above, "Sub" is replaced with "Function", the code works the same, but the macro does not appear in the list of available macros. The downside of this method is that the function will appear in the functions list.(Generally used for calculations and returning a value.) I sometimes prefer this to keep overview in the macros list. If "Private" is used before "Sub" or "Function", it can only be called from the same module, whilst also not being visible in the list.
Beware that the code shown on this page is made to look correct on your screen. Using copy/paste may result in not working code because some characters have been replaced because of the xhtml code
For example: a shown "&" on this page will show as "&" when copied.
|myRow = ActiveCell.Row
myCol = ActiveCell.Column
MsgBox myRow & "," & myCol
|Shows Active Cell position.|
Selection to far left
Range("A" & ActiveCell.Row).Select
|The top one takes the active cell to the far left, but not if there is an empty cell in between. To counter that the bottom one works better.|
|Active Cell in top left of screen||With ActiveWindow
.ScrollColumn = ActiveCell.Column
.ScrollRow = ActiveCell.Row
|Scrolls to place the ActiveCell be at the top & left of the screen|
|Active Cell, Value||MsgBox ActiveCell.Value||Shows the value of the current active cell|
|Auto Run (1)||Sub Auto_Open()
|Making your macros run automatically when opening your workbook. This macro will display the message "Hello" when you open the workbook. This code would be located in the module.|
|Auto Run (2)||Sub Workbook_Open()
|For this second method, giving the same result as the previous one, the code must be in the workbook.|
|Auto Run (3)||Sub Worksheet_Activate()
|This one starts on activating a sheet.|
|Auto Save||This workbook: ' = Autorun
Application.OnTime Now + Timeserial(0,15,0),"SaveMe" ' Timeserial=(h,m,s)
|Saves the file every 15 min in this case.|
|ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Offset(1, 0).Select||Here we go to the bottom line of the active sheet, then up on the first row containing something in column "A" , then one down again.|
Running a subroutine
|Call Macro2 'This calls for Macro2 to run within your Macro||The use of the "Call" keyword is optional. And it may be used for readability of your code.|
There is a catch... Using "Call" requires possible arguments to be enclosed in parentheses.
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = Application.Proper(cell)
|Upper case first letter of each word in text.|
|Case Upper / Lower||Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = UCase(cell)
|To change text in a selected range to upper case, or if you want lower case, replace UCase with LCase.|
|Column Letters||Dim MyColumn As String, Here As String
Here = ActiveCell.Address
MyColumn = Mid(Here, InStr(Here, "$") + 1, InStr(2, Here, "$") - 2)
|Shows the column letter(s) from the active cell.|
|Counting Rows & Columns||myRows = Selection.Rows.Count
myColumns = Selection.Columns.Count
MsgBox "Rows = " & myRows & vbCrLf & "Colums = " & myColumns
|Showing the number of Rows and Columns in a selection.|
|Carriage Return||MsgBox "Line 1" & vbCrLf & "Line 2"||Splits text in two rows|
|Copy Range (1)||Range("A1:B1").Copy
Application.CutCopyMode = False
|You get this when recording the macro. Note the use of "Application.CutCopyMode = False" to clear the memory|
|Copy Range (2)||Sheet1.Range("A1:C1").Copy Destination:=Sheet2.Range("A1")||Copy data from Sheet1 to Sheet2.|
Please note that unlike the above copy example, there is no need to clear the memory by "Application.CutCopyMode = False"
Also note that a sheet has in fact two names, both visible in VBA properties. Prefer using the "Sheet X" in VBA since the visible one's name can be changed.
|Copy Range (3)||Sheet2.Range("A1:C1").Value = Sheet1.Range("A1:C1").Value||Similar but even shorter to the above, this would be the better way to Copy.|
|Counter||Range("A1") = Range("A1") + 1
myCount = Range("a1") + 1
Range("a1") = myCount
|Basic counter, using cell A1 to display the result. Here adding 1 each time the macro is used. The second code does the same, but might sometimes be less confusing.|
|Current Date||Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range("A1") = Now
|Place this code into "This Workbook" and it will place the date and time when saving.
This is handy for knowing you are working on the latest version of a file.
|CurrentRegion||Sheet1.Range("A1").CurrentRegion.Select||The current region is a range bounded by any combination of blank rows and blank columns.|
Alternatively, check out Available Row if the region might contain empty rows or columns.
|CurrentRegion without header||Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select
|Select Current Region without header|
|Error Trapping||On Error Resume Next
On Error Goto ErrorHandler1
... more lines of code
... code specifying action on error
|Trapping errors are important as users can do marvelous things to mess up you macros. Here you can use either of these 2 statements.|
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.
|File Name & Path||Range("A1") = Application.ActiveWorkbook.FullName||Displays the full file name and path|
|For, Next Loop||See Deleting Empty Rows. General advise is against using for next loops since they can slow things down considerably. Try to find an alternative if possible.|
|Goto (Code)||See Error Trapping|
|Input Box||Dim MyInput
MyInput = InputBox("Enter something")
Range("A1") = MyInput
|Get user input during macro execution.|
|If, Then Statement||If Range("B1") > 10 Then
Range("B2") = 10
ElseIf Range("B2") > 5 Then
Range("B2") = 5
Range("B2") = 1
|To be used in more variations. Add or delete ElseIf and Else if need be.|
Also see the "Select Case" statement.
|Joining Text||myCol = Selection.Columns.Count - 1
n = 0
For n = 0 To Selection.Rows.Count - 1
For i = 1 To myCol
ActiveCell.Offset(n, 0) = ActiveCell.Offset(n, 0) & ActiveCell.Offset(n, i)
ActiveCell.Offset(n, i) = ""
|Join text on each row in a selection.|
|Message Box||MsgBox "Created by: Your Name here"
MsgBox "Different Icon", vbInformation
MsgBox "Different Icon And Title", vbExclamation, "Your warning message"
|Here three different MsgBox styles are shown. The buttons can also be customised to show The "OK" button, or the "Yes / No" buttons .(Refer to vbYesNo macro)|
|Modeless Forms||UserForm.show vbModeless||Allow users to switch from your form to the spreadsheet by clicking on either one. Usually, this is set beforehand in the properties window. Thought it might be useful to mention here also.|
|Offset||ActiveCell.Offset(1, 0).Select||(1, 0) = Down 1, (-1, 0) = Up 1, (0, 1) = Right 1 and finally, (0, -1) = Left one|
|Protecting / Unprotecting a sheet||'Protect
Password = "xxxx"
ActiveSheet.Protect Password, True, True, True
Password = "xxxx"
'Protect, but allow changes by VBA:
Sheet1.Protect Password:="xxxx", UserInterfaceOnly:=True: Sheet1.EnableSelection = xlNoRestrictions
|This macro will protect/unprotect the current worksheet with a password.|
|Random numbers||MyNumber = Int((10 - 1 + 1) * Rnd + 1)
Range("A1") = MyNumber
|For macros to generate random numbers within limits, use this code: Int ((upperbound - lowerbound +1) * Rnd + lowerbound). Where the Upperbound is the largest number random number to be generated and Lowerbound is the lowest.|
In the left example code, the random numbers that will be generated range from 1 to 10.
|Rounding Numbers||ActiveCell = Application.round(ActiveCell, 2)||Rounds to two digits behind the comma.|
|Saving your Workbook||ActiveWorkbook.Save||Save your Workbook automatically after running a Macro.|
|ScreenUpdating||Application.ScreenUpdating = False / True||"False" Ensures the screen does not flicker whilst running the macro. Don't forget to set to "True" at the end.|
|Select Case statement||Select Case Range("A1").Value
Case 100, 150 ' = 100 OR 150
Range("B1").Value = Range("A1").Value
Case 200 To 300, 400 To 500 ' = Between 200 and 300 OR between 400 and 500
Range("B2").Value = Range("A1").Value
Range("B1").Value = 0
|Similar to the "If, Then" statement, "Select Case" statement is sometimes to be preferred for better view.|
|Select Data Range||Dim myLastRow As Long
Dim myLastColumn As Long
On Error Resume Next
myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
myRange = "a1:" & Cells(myLastRow, myLastColumn).Address
|This is useful when you need to select the whole range of your data.|
|Sheets Hiding||Sheet1.Visible = xlSheetVeryHidden||To hide your worksheet from users you can use this code.|
Hiding your sheets this way, prevents users to unhide them using the menus. Only using VBA codes will be able to display the sheets again.
If you want to remain able to make the sheet visible again from the menus, use something like this:
Sheet1.Visible = False
|Text Edit||MsgBox Left("abcd", 2) 'Displays 2 characters from Left
MsgBox Right("abcd", 2) 'Displays 2 characters from Right
MsgBox Len("abcd") 'Displays number of characters (Including space)
|Here are some useful text functions which you could use to EDIT your text.|
|Timer 1||Application.Wait Now + TimeValue("00:00:05")
MsgBox ("This was a 5 second delay")
|Timer puts a delay in executing your code, but displays an hourglass during the time.|
|Timer 2||Application.OnTime Now + TimeValue("00:00:02"), "Macro2"||After the set time, Macro2 is executed. This code does not show the hourglass.|
|vbYesNo||YesNo = MsgBox("This macro will ... Do you want to continue?", vbYesNo + vbCritical, "Caution")
Select Case YesNo
'Insert your "Yes" code here.
'Insert your "No" code here.
|Users need to click Yes or No|
If the Macros are for your own use, security is usually no problem. However, if you make a Workbook and macros to be used by others, you will find that anything that can go wrong, will go wrong. This means that security needs to get some serious attention. The following is usually needed at least then:
1) Hide sheets that are not needed for view by the users
2) Protect your Workbook and Sheets with passwords
3) Protect your VBA code: 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.
Please keep in mind that I am an occasional user of Excel and VBA, which is the reason I made this overview.
In case you need to contact me about it: rob (remove this and join) (at) panzerbasics (dot) com.