If you write VBA macros that run on other users’ machines, you should consider including in your code some simple safeguards against user error. Users often assign macros to toolbar buttons, which they can easily click by mistake. Depending on the function of your macro, this could be harmful, particularly since Office’s native Undo command can’t revert changes made by a macro. Of course, you don’t want to give users a choice about some of your code, such as AutoOpen macros. But for other optional macros, you can ask users to confirm that they want to run the code by including a function that presents a simple dialog box at runtime.
A little background on functions
A function is a special sort of macro that is called by another macro and returns some value to the macro that called it. Using function calls to replicate tasks across numerous macros instead of typing the same code over and over is a great timesaving approach. It takes just a few lines of code to call the function and evaluate the returned value.
Our simple function Confirm asks the user if it’s okay to run your macro. The macro calls the function, which returns the user’s answer. It then evaluates the answer and decides if it should continue.
Confirmaccepts one argument, MsgText, which the function uses to display a message to the user. The sample macro below, Call_Confirm, passes the MsgText value “Would you like to run my cool macro?” to the Confirm function. When Call_Confirm runs, it will cause the Confirm function to display the dialog box shown in Figure A.
|The Confirm function presents this dialog box and passes the user’s response back to the calling macro.|
If the user answers “No,” the function returns a False value and the user sees the message “You have chosen not to run the macro.” If the answer is “Yes,” the function returns a True value and the macro will run whatever code you’ve inserted in place of “Guts of your macro goes here.”
The Call_Confirm macro
‘This shows an example of how to call the Confirm Function
If Confirm(MsgText:="Would you like to run my cool macro?")= False Then
MsgBox Prompt:="You have chosen not to run the macro”
‘Guts of your macro goes here
Building your own functions
Building your own functions is easy. As you can see in the listing below, you call the code a Function instead of a Sub and then place your argument declarations inside the following parentheses (). Somewhere in the function, you place a statement that sets an argument with the same name as the function. This argument holds the value that the function passes back to the calling macro.
Consider this example:
Function TestingThis(TestValue asLong)
TestingThis = TestValue /2
If you called this function and sent it the number 10 in the TestValue argument, it would return the number 5, given that 10/2 = 5.
Function Confirm(MsgText As String) As Boolean
Dim lngConfirm As Long
`This line makes the value of lngConfirm equal the result of the MsgBox function
‘6 is the value returned by the ‘Yes’ button
‘7 is the value returned by the ‘No’ button
lngConfirm = MsgBox(Prompt:=MsgText, _
‘The code below checks the value of the lngConfirm variable
‘If it = 6 (the value for YES from the MsgBox function) then it
‘sets the value of the function to True. If it = 7 (the value for NO) then
‘the value of the function is set to False.
If lngConfirm = 6 Then
Confirm = True
ElseIf lngConfirm = 7 Then
Confirm = False
More experienced programmers out there probably know that you don’t need to have a function like the one we just discussed to confirm a macro run with users. You could just use the MsgBox function in the body of the macro and test for a return of the default numeric values of either 6 or 7. But I’ve found that many new programmers get a bit confused by the different numbers MsgBox can return based on the Buttons argument. Using functions for even a fairly simple task, such as user confirmation, serves two purposes. First, it provides an easy way to use and remember a confirmation function that returns a simple True or False value. Second, it gets programmers into the habit of thinking about new ways to write their code so that it can be reused instead of constantly rewritten.
Microsoft Project question
Columnist Brian Kennemer has created several useful macros for Microsoft Project based on suggestions from members. Send us an e-mail to suggest other macros, tips, and tricks that you would like Brian to address.