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. 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.
| Activate | Sub Worksheet_Activate() MsgBox "Hello" End Sub |
Similar to Auto Run, this one starts on activating a sheet. |
| Active Cell, Position | myRow = ActiveCell.Row myCol = ActiveCell.Column MsgBox myRow & "," & myCol |
Shows Active Cell position. |
| Active Cell, Selection to far left | Selection.End(xlToLeft).Select OR 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 End With |
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 | Sub Auto_Open() MsgBox "Hello" End Sub |
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() MsgBox "Hello" End Sub |
For this second method, giving the same result as the previous one, the code must be in the workbook. |
| Auto Save | This workbook: ' = Autorun Sub Workbook_Open() Call SaveMe End Sub In Module: Sub SaveMe() ThisWorkbook.Save Application.OnTime Now + Timeserial(0,15,0),"SaveMe" ' Timeserial=(h,m,s) End Sub |
Saves the file every 15 min in this case. |
| Available Row (next) | Range("a65536").End(xlUp).Offset(1, 0).Select |
Here we go to the bottom line, then up on the next row and one down again. Just make sure to choose a column that is not empty. |
| Call - Running a subroutine |
Call Macro2 'This calls for Macro2 to run within your Macro | To run another macro from within a macro you need to use the Call statement. |
| Case Title |
Dim cell As Range For Each cell In Selection.Cells If cell.HasFormula = False Then cell = Application.Proper(cell) End If Next |
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) End If Next |
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) MsgBox MyColumn |
Shows the column letters 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) | Sheet1.Range("A1:C1").Copy Destination:=Sheet2.Range("A1") | Copy data from a specific range can be done with this macro. Here data is copied from Sheet1 to Sheet2. Please note that a sheet has in fact two names, both visible in VBA properties. Always refer to the top one in VBA since the other one is more often changed. |
| Copy Range (2) | Range("A1:B1").Copy Range("A2").PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False |
Another method for copying. This time using PasteSpecial |
| Counter | Range("A1") = Range("A1") + 1 OR 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 End Sub |
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. |
| Delete Empty Rows | firstRow = Selection.Row lastRow = Selection.Row + Selection.Rows.Count For rownumber = lastRow To firstRow Step -1 If Application.WorksheetFunction.CountA(Rows(rownumber)) = 0 _ Then Rows(rownumber).Delete Next rownumber |
Delete empty rows in a selection |
| Error Trapping | On Error Resume Next OR Sub Name() On Error Goto ErrorHandler1 ... more lines of code Exit Sub ErrorHandler1: ... code specifying action on error End Sub |
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 Else Range("B2") = 1 End If |
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) = "" Next i Next n |
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. |
| Moving the Cursor | 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 Dim Password Password = "xxxx" ActiveSheet.Protect Password, True, True, True 'Unprotect Password = "xxxx" ActiveSheet.Unprotect Password |
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 Case Else Range("B1").Value = 0 End Select |
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 Range("A1").Select On Error Resume Next myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row myLastColumn = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column myRange = "a1:" & Cells(myLastRow, myLastColumn).Address Range(myRange).Select |
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 | Application.Wait Now + TimeValue("00:00:05") MsgBox ("This was a 5 second delay") |
Timer puts a delay in executing your code. |
| Time last save | MsgBox Excel.Application.ThisWorkbook.BuiltinDocumentProperties("Last Save Time") | Displays when the document was last saved |
| vbYesNo | YesNo = MsgBox("This macro will ... Do you want to continue?", vbYesNo + vbCritical, "Caution") Select Case YesNo Case vbYes 'Insert your "Yes" code here. Case vbNo 'Insert your "No" code here. End Select |
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.
