VBA, Basic Excel Macro collection

Being a beginner at VBA programming myself, I need to archive the ones I tried and found useful myself. Putting them online was only a small step further.

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.

 

Description Code Remarks
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 (1) 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 Run (3) Sub Worksheet_Activate()
MsgBox "Hello"
End Sub
This one starts on activating a sheet.
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.
(next)
Available Row
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.
Call -
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.
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 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
Range("A2").PasteSpecial Paste:=xlPasteValues
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
 
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.
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
 
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.
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
Dim Password
Password = "xxxx"
ActiveSheet.Protect Password, True, True, True
 
'Unprotect
Password = "xxxx"
ActiveSheet.UnProtect Password
 
'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
   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 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
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.