Call Private Sub

[AMAS]

I am trying to call this private sub but its not working. Any thoughts.

This works:

Call FormatCells(cell, myList, myColor)

This doesn't work (doesn't compile):

Application.Run "FormatCells" (cell, myList, myColor)

This doesn't work (compiles but doesn't do anything):

Application.Run "FormatCells, cell, myList, myColor"

AMAS

 

[villy]

maybe you can just write.

Call FormatCells

 

[AMAS]

Hi,

'Call' works great with its a regular sub. I am trying to hide all my subs and make them Private Subs. 'Call' seems to only 'see' regular subs, but not private subs.

AMAS

 

[GTO]

I am trying to call this private sub but its not working. Any thoughts.

This works:

Call FormatCells(cell, myList, myColor)

This doesn't work (doesn't compile):

Application.Run "FormatCells" (cell, myList, myColor)

This doesn't work (compiles but doesn't do anything):

Application.Run "FormatCells, cell, myList, myColor"

AMAS

Are you saying that the first one works if FormatCells() is Private?

 

[AMAS]

No. Call works as long as its Sub FormatCells() but not if its Private Sub FormatCells().

AMAS

 

[Hermanito]

You can only call private subs or functions when you are doing it in the same module.

If by 'hide', you mean make sure they don't show up in the play macro listbox, you can use some other tricks:
1) Change Sub into Function, without changing anything else
2) Make the Sub to have a parameter, even if you don't use it

 

[RoryA]

Application.Run "FormatCells", cell, myList, mycolor

would be the syntax.

 

[Rick Rothstein]

I have to ask... what functionality do you think making your subs Private provides for you that has made you so insistent on wanting to use them that way?

 

[GTO]

Hi,

'Call' works great with its a regular sub. I am trying to hide all my subs and make them Private Subs. 'Call' seems to only 'see' regular subs, but not private subs.

AMAS

Okay, if the procedure being called is located in a different module than the calling procedure, the called procedure cannot be Private.

I am not quite understanding on why the need to hide, as your listed procedure has arguments and thus, will not show in the macros dialog box. Am I missing something?

 

[AMAS]

Thanks everyone for your interest. This has been a learning experience for me.

Rorya: That was the correct syntax. Thank you.

Hermanito: Thank you for this great advice. I will keep it on hand and try to use the different options.

Rick: The reason I wanted to hide the subs was because I have a file consisted of around 10 modules and 30 subs. The macro listbox started to get crowded for no reason since most of the subs call each other. I was trying to find a way of housekeeping and tiding up.

GTO: You know I never noticed this. Thanks for pointing this out to me. Sometimes we miss the obvious.

Thanks again everyone.

AMAS