Excel VBA Toolbox

This section contains a whole host of Excel VBA routines, techniques and code that don’t really warrant having a page of their own but are very useful none the less.

Whilst these routines have been tested in Excel, many of them will also work in the other VBA enabled Office applications exactly as they are, or with a little tweaking. Check out similar pages on our Access, Word, PowerPoint and Outlook sections for code that may also work in Excel.


Creating a confirmation box for an action

You can use this little routine to provide a Yes / No confirmation box to add to any action a user might take.

If Msgbox(“Are you sure?”,vbyesno+vbquestion,”Please confirm”)= vbyes then
‘do the required action
End If

You can of course change the “Are you sure” and “Please confirm” statements for something more descriptive.


Finding the serial number of the drive on which your worksheet is running

 

Sub GetSerialNo()
Dim Ds As Object
Dim Bs As Object
Dim C As String
Set Ds = CreateObject(“Scripting.FileSystemObject”)
Set Bs = Ds.GetDrive(Ds.GetDriveName(Ds.GetAbsolutePathName(ThisWorkbook.Path)))
C = Bs.SerialNumber
MsgBox (“Drive Serial Number is: ” + C)
End Sub


Assigning a Macro / Code to a specific cell

Sometimes it can be useful to have some code execute if a specific cell is changed, Excel doesn’t have an ‘On Change’ or ‘On Update’ event, this little routine will simulate such an event, assuming you want to trap a change to cell A1

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo wsheet_exit
If Target.Address = “$A$1” Then
Application.EnableEvents = False
‘Now Put your code here
End If

wsheet_exit:
Application.EnableEvents = True
End Sub


Automatically Name Your Sheet

If you have a workbook that you add many new sheets to, it can be useful to have a sheet name itself based on the contents of a given cell, this little routine does that, by using the contents of Cell A1 as the name for your sheet.

Sub NameSheet()
ActiveSheet.Name = Range(“A1”).Value
End Sub