CODEWITHSUNDEEP
excelaccess-modifiersvba
user429400
user429400

Reputation: 3325

vba excel write sub which is accessible for both modules but invisible to the user

I want to move some of my util subs into a different module. The problem is that if I do it I must set their access modifier to be "public" and then they are visible to the user when he presses the "macros" button.

What should I do? How can I use the Utils module without making its subs visible to the users?

Thanks, Li

Upvotes: 2

Views: 1019

Answers (2)

MathKid
MathKid

Reputation: 2112

Save your utils modules as an excel Addin, instead of a workbook. Make sure the file name ends in .xla or .xlam. This way only other vba code in excel addins and workbooks can see them and they will be invisible to the user.

Alternatively, if there is just one Sub you want to hide or saving as an .xla is not an option. You could give your Sub an optional parameter

Option Explicit

public Sub Sample1(Optional x as string = "")
    ' Code goes here, just as before.
End Sub

then it won't show in the run macro dialog, even if it's public.

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149287

Declare the subs as Private so that they are not visible to the users. And then if you want to call them, use Application.Run

For example

Module 1

Option Explicit

Sub Sample1()
    Application.Run "Module2.Sample2"
End Sub

Module2

Option Explicit

Private Sub Sample2()
    MsgBox "Hello World"
End Sub

Upvotes: 2

Related Questions