Option Private Module/code organisation

[Gzdnkh]

Hello, I had no idea how to write a good title for this problem.

I have a workbook with many different sheets with different macros needed in each sheet.
Sometimes a sheet is used as a template - VeryHidden and copied when needed.
Not knowing any better I put my code specific to each sheet in: VBAProject\Microsoft Excel Objects\Sheet...\"View Code" and it worked.

Now I would like a macro from one sheet to call a procedure in another sheet.
Which is easy if that procedure is "Public":

Call Worksheets("Sheet1").testProcedure

But then that procedure is Public which I do not want (User can try to run it directly from Excel.

I found that I should use "Option Private Module" which works in "VBAProject\Modules".
But in Excel objects I get a compile error "Option Private Module not permitted in an object module". And I am stuck.

I have two problems:
1. Can it work the way I want it to (a Public procedure in object module not seen by the user)
2. Is my concept of organizing macros, code is wrong? Are there any tips/tutorials telling how to do it?

[abousetta]

You might need some re-organization of your subs. Generally, I prefer to one module and make the subs private (e.g. Private Sub Test) and call it from within the same module (e.g. Application.Run "Test"). It all depends on what each sub/ function does but I tend to stay away from sheet-level code unless it's specifically needed (e.g. triggered by a worksheet event).

abousetta

[TMS]

Why not put the common code in a Private module and then call it from both sheets?

Regards, TMS

[:) Sixthsense :)]

Just tested in this way and its calling even though the procedure is private and picking nicely from any of the module

Application.OnTime Now, "YourMacroNameHere"

[JosephP]

you may also declare the sheet procedures as Friend

[Gzdnkh]

Thank you for all the help
@josephP: I have tried "Friend" before and it does not work. The same error as with Private.

@sixthsense and abousetta: both "Run" and "OnTime" work. A pity I can't pass Variables as with procedures.
I have a follow up. How to elegantly get strYourMacroNameHere?
Something like:

Worksheets(strSheetName).strSubName

would be nice, but so far I have not found a way to make it work

Right now I can think only of something like:

Do Until Worksheets(intCounter).Name = strSheetName Or intCounter Is Worksheets.Count
   intCounter = intCounter + 1
Loop
strYourMacroNameHere = "Sheets" & intCounter & "." & "strSubName"

@TMShucks: That will work in half of the cases, when the procedure is common for all the sheets. The other half of cases would be like:
I will have several sheets which will need procedures for protecting them, each sheet, different ranges, settings. I imagined creating a procedure called "Protect" in each sheet so i can just use "Call Worksheets(strSheetName).Protect" in main body, when needed. Right now I use "Select Case" and it annoys me

[Kyle123]

Can you put your procedures in a class? That would create sone kind of vba namespace thingy. Your calling code would need to init the class to use it, but its procedures would be out of scope generally. This of course all depends on how your code is structured

[JosephP]

the attached works for me-only the public sub in sheet1 is visible in the macros dialog but all three can be called (in various ways) from sheet2

[TMS]

Thanks for the rep.

I generally do not want my code to be visible to the user and/or called manually. If I do, I put it in a standard module that does not have Option Private Module.

The user cannot see, or call, standard event handlers in sheet or workbook modules.
The user cannot see subroutines in a Private Module, but they can run them if they know they are there.
The user cannot see Private subroutines in a standard Module, but they can run them if they know they are there.
The user cannot see, or call, subroutines that take parameters. So you could add an optional parameter that you do not use.

Protect your code with a Password to View and it will be difficult for anyone to know what might be lurking behind the scenes.

Regards, TMS

[JosephP]

The user cannot see, or call, subroutines that take parameters. So you could add an optional parameter that you do not use.

depends on the parameter-for simple data types if the user knows the routine name he/she can call it from the dialog as with private routines

[TMS]

@JP: thanks for the insight. Never tried that. But, as ever, the user needs to be aware that the subroutine is there and how to call it. I guess most won't.

Regards, TMS

[Gzdnkh]

Thank you all again, I have to digest the information. For now solved