When you add a macro to an Excel workbook, users can see that macro in a list when they choose Tools|Macro|Macros.
Hide Your Macros
In some workbooks, you might want to hide one or more of your macros, to prevent users from running them.
Note: This technique won’t add any protection to your code, it will simply remove the macro from the list, so casual users won’t accidentally or intentionally run it.
Make the Macro Private
In this example, I have two macros – one that the users can run, and one that I’d prefer to keep hidden.
Here’s the original Excel VBA code:
Revised VBA Code
If I add the word Private at the start of the Sub MySecretMacro line, that macro will NOT be included in the macro list.
Private Macro Not in List
Now only the MyVisibleMacro appears in the list.
Private Macro Not in List
Run the Hidden Macro
Now that you’ve hidden the macro, so casual users won’t find it, how can you run it yourself? Here are a few ways you can run it
Open the Visual Basic Editor (Alt+F11), click somewhere in the macro’s code, then click the Run button.
OR
If you know the name of the macro, type it in the Name Box, at the left of the Formula bar.
Press the Enter key, and the Visual Basic Editor will open automatically, with the cursor flashing in the macro’s code
Click the Run button.
OR
Before you hide the macro, choose Tools|Macro|Macros, select the macro, and click the Options button
Type a keyboard shortcut (Ctrl + Shift + M in this example) then click OK
Close the Macro dialog box.
Add Private to the macro code, then use your keyboard shortcut to run it when required.
On my website there are sample Excel files, many of which use VBA event programming to make things happen.
What is an Event?
For Excel VBA program, an event procedure is code that runs automatically, after an “event” occurs.
An event is something that happens in an Excel workbook or worksheet. For example:
Type something in a cell (worksheet change event)
Click on a cell to select it (worksheet selection change event)
Click on a worksheet tab to select it (worksheet activate event)
In the Excel Visual Basic Editor, if you’re working on a Worksheet module, you can see a list of the worksheet events.
Excel VBA Worksheet Events
Worksheet Event Code
With event programming, it’s easy to automate your worksheets and workbooks. People don’t have to remember to click a button, or run a macro from the Macro list.
Instead, your code runs automatically when a specific event occurs. People just need to change a cell, or refresh a pivot table, and the code for that event will run.
Event Programming Example
For example, on this worksheet there’s a data validation drop down list in cell B2, where a day of the week can be selected.
data validation drop down list with weekdays
Select a Weekday
When you select a weekday, that changes the worksheet.
If the worksheet has VBA code for the Worksheet_Change event, it will run automatically, if any cell on the worksheet is changed
In this example, the Worksheet_Change event code checks the address of the cell that was changed, to see if it was cell B2.
If it was cell B2, a message is displayed — “Please update the daily roster.”
If any other cell was changed, nothing happens
View the Worksheet Code
To see the code for a worksheet, right-click on its sheet tab, and click View Code.
The Visual Basic Editor opens, and shows the code for the active sheet.
Multiple Worksheet Events
What happens if you try to create multiple Worksheet_Change events on the same worksheet?
For example that message code for cell B2 works well, so you’d like to add a similar Worksheet_Change event, for a different cell.
If you make a change in cell B4, you’d like the date automatically entered in cell B5.
Visual Basic Error Message
Unfortunately, if you add another Worksheet_Change event, you’ll see an error message when you change a cell.
In the screen shot below, the VBA error message says:
Compile error: Ambiguous name detected: Worksheet_Change
That message appears if you use the same procedure name twice on the worksheet.
Compile error: Ambiguous name detected: Worksheet_Change
Combine the Event Code
Instead of creating separate events with the same name, combine both pieces of code into one.
For example, you could use Select Case, and specify what should happen when specific cells are changed.
Some situations will require a more complex solution, and if you experiment a bit, you should be able to include multiple outcomes within a single worksheet event’s code.
______________________
Do you write Excel macro, with VBA programming? Is your code clean?
Clean Code Wristband
While I was browsing the Internet recently, I saw someone promoting a “Clean Code” wristband, with donations going to a non-profit group.
Writing clean code is an idea worth supporting, and you could snap that wristband, to wake yourself up, if your coding starts to go off track.
Excel VBA Tutorials
If you’d like to get started with Excel VBA programming, there are several pages on my Contextures site, where you can find tutorials, sample files, and videos.
1 — How to Add Macro Code to Excel Workbook — How to copy Excel macro VBA code to your workbook, from website or sample file. Different types of code, where to paste it. Step-by-step videos, written steps
2 — Excel Macro to Save Sheets As PDF — Excel macro saves active sheet or sheets in PDF format, prompts for file name and folder. See macro code, get sample workbook with macro.
3 — How to Create Excel UserForm for Data Entry — See how to create an Excel UserForm for data entry, with text boxes and buttons. Follow step-by-step videos, detailed notes with screen shots, free Excel workbook
Unlike Word, excel doesn’t have a menu command that will insert the last saved date.
However, you can use a bit of programming to insert the date, assuming the workbook has been saved. There is sample code in the next section.
Note: You could also create a User Defined Function (UDF) to calculate the date. However, I find that UDFs can slow down a workbook, so avoid them, if possible.
Macro VBA Code – Last Saved Date
Here is the code that inserts the workbook’s last saved date. It’s entered on a sheet named Data Entry, in cell A1.
Tip: You could add an event procedure in your workbook, so this code runs automatically, any time the workbook is saved.
Sub GetLastSavedDate()
On Error Resume Next
Dim sSaveDate As String
sSaveDate = FileDateTime(ActiveWorkbook.FullName)
If sSaveDate = “” Then
MsgBox “Could not determine save date.”
Else
Worksheets(“DataEntry”).Range(“A1”).Value _
= “Last Saved: ” & sSaveDate
End If
End Sub
When answering newsgroup Excel questions, or doing testing for client projects, I often create a quick table, with a few rows and columns.
The headings, such as a list of months or weekdays can be generated by using AutoFill. That just leaves the body of the table, where I want some numbers for the test.
Create Random Numbers
I used to calculate numbers with a RAND or RANDBETWEEN formula, then copy and paste the results as values.
But that’s a lot of steps when you’re in a hurry, so I built my own number generator, that runs with a click or two.
The values aren’t important, I just want something to populate a table, pivot table or quick chart.
Excel Macros for Random Numbers
I wrote macros to generate numbers in different ranges, and added these to a toolbar menu.
You could add toolbar buttons, or assign shortcut keys to run them. In Excel 2007, add them to the Quick Access Toolbar.
Excel Macros for Random Numbers
Excel VBA Code
My macro code is shown below. Do you have a different way to create dummy numbers for testing?
'====================================
Sub SRandRange()
Dim c As Range
Dim x As Long, y As Long
Application.EnableEvents = False
Application.ScreenUpdating = False
x = Application.InputBox("Start Number")
y = Application.InputBox("End Number")
For Each c In Selection
c.Value = randbetween(x, y)
Next
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
'====================================
Sub SRand10()
Dim c As Range
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each c In Selection
c.Value = Evaluate("=ROUND(RAND()*10,0)")
Next c
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
'====================================
