Microsoft Office Tutorials and References
In Depth Information
Executing Sub Procedures
The next example uses the Run method to execute the UpdateSheet procedure and then to
pass MonthNum as the argument.
Sub MySub()
MonthNum = InputBox(“Enter the month number: “)
Application.Run “UpdateSheet”, MonthNum
‘ ... [code goes here] ...
End Sub
Sub UpdateSheet(MonthSeq)
‘ ... [code goes here] ...
End Sub
Perhaps the best reason to use the Run method is when the procedure name is assigned to a
variable. In fact, it’s the only way to execute a procedure in such a way. The following example
demonstrates this. The Main procedure uses the VBA WeekDay function to determine the day of
the week (an integer between 1 and 7, beginning with Sunday). The SubToCall variable is
assigned a string that represents a procedure name. The Run method then calls the appropriate
procedure (either WeekEnd or Daily ).
Sub Main()
Dim SubToCall As String
Select Case WeekDay(Now)
Case 1, 7: SubToCall = “WeekEnd”
Case Else: SubToCall = “Daily”
End Select
Application.Run SubToCall
End Sub
Sub WeekEnd()
MsgBox “Today is a weekend”
‘ Code to execute on the weekend
‘ goes here
End Sub
Sub Daily()
MsgBox “Today is not a weekend”
‘ Code to execute on the weekdays
‘ goes here
End Sub
Calling a procedure in a different module
If VBA can’t locate a called procedure in the current module, it looks for public procedures in
other modules in the same project.
If you need to call a private procedure from another procedure, both procedures must reside in
the same module.
Search JabSto ::

Custom Search