Excel With VBA

VBA Procedure

This is an example of the VBA Procedure:

Scope is an important topic when it comes to VBA procedures. The topic of scope deals with what can “see” the VBA procedure. Can the workbook “see” the VBA procedure? Can only the modules in the VBA procedure see the VBA procedure?

VBA Procedures can be “Private” or “Public”

If you are creating a VBA procedure, also called a macro in VBA, and want to call it from a button on your worksheet, make sure the procedure is “Public”. If the procedure is “Private” you will not be able to see the newly generated procedure when you assign the macro to the newly created button. (See the video below)

***************************************************************

Private Sub NewMsg() 

      Dim strMsg as String 'Allocate a portion of memory for a string (text - up to 255 characters) holding variable 

      strMsg = "Press enter to continue" 

      Msgbox strMsg 

End Sub

***************************************************************
The prior VBA procedure cannot be seen from objects outside of the VBA window (Like the “Assign Macro” window).
***************************************************************

Public Sub NewMsg() 

Dim strMsg as String 'Allocate a portion of memory for a string (text - up to 255 characters) holding variable 

strMsg = "Press enter to continue" 

Msgbox strMsg 

End Sub

***************************************************************
The prior VBA procedure can be seen from objects outside of the VBA window (Like the “Assign Macro” window).
***********************************************************************

Now watch how it’s done…

Please note that by default the procedures are Public. So “Public Sub NewMsg()” is the same as “Sub NewMsg()”.

That’s all, I hope it helped! If not, feel free to contact me with the form below.

[simple_contact_form]

admin