Using The Visual Basic Editor

You should hide macros that are called by other macros if you do not want the macros to execute alone from the Macro dialog box. For example, if you have a macro named ChangeCells that calls another macro named AddCellValues, you can hide the AddCellValues macro so that a user cannot select that macro from the Macro dialog box. When you mark a procedure as private by placing the Private statement in front of the Sub statement for the subroutine, you can only access the subroutine within the same code module. In other words, you must place the subroutine that corresponds to the macro calling the hidden macro within the same code module as the hidden macro. See the sections "Create a Subroutine" and "Create a Function" for more information on using the Private statement.

To make a hidden macro visible again, you need to access the module containing the corresponding subroutine within the Visual Basic Editor and delete the Private statement in front of the Sub statement. Because you cannot access a hidden macro from the Macro dialog box, the only way to access the Visual Basic Editor is to click Tools O Macro O Visual Basic Editor.


Lools Add-Ins Window Help

ThisWorkbook : S ® Modules

: ■■«ft Module 1 ffl -^ VBAProject (OfficeExr Q VBAProject (PERSONA i'Q Microsoft Excel Objec S-Q Modules

ThisWorkboot Workbook Alphabetic | Categorized |


lonflictResolutii 1 - xlUserReso Date 1904 False DisplayDrawingC -4104 - xlDispl.-EnableAutoRecc True [nvelopeVisible False

MighlightChange False

KeepChangeHist True


■ The Visual Basic Editor opens and displays the module containing the macro you selected.

Standard").Visible = Fa idard").Visible = Tr natting") .Visible = • 3-D Settings").Visible ■ Settings").Visible l iecs") .Visible = Trui Jets") .Visible = Fal;

File Edit View Insert Format lools Data Window Help Type a question for help - . S x

Monthly Expenses

Groceries Fuel Electricity Child Can Misc.

March April






110 110

OfficeExpenses.xls! Change_Font PERSONAL .XLS I Sum_Cells Expenses.xls! SumCells

I|aII Open Workbooks

Delete 3 Options...

Macro recorded 6j 10/2001 by Jinjer Simon

□ Type Private before the Sub statement.

Close the Visual Basic Editor.

Q Open the Macro dialog box.

■ The macro no longer displays on the Macro dialog box.

0 0

Post a comment