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
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
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)
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
Application.EnableEvents = True
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.
ActiveSheet.Name = Range(“A1”).Value