VBA- Option for forcing explicit module name when invoking Sub

[tfield98]

Is there a way for me to specify that all Sub or Function invocations have to be explicitly prefixed by a module name?

For example, if Module MyModule contains

Sub MySub()
End

Then, when anywhere in the code (including MyModule) where today I use the line

MySub()

I want to be forced to use MyModule.MySub()

TIA

 

[xenou]

Nope, that can't be forced - it's up to you

 

[tfield98]

Thanks for the response. So, how does the VBA runtime resolve (scope) when two modules define a Sub with the same name and parameters and a third module invokes the Sub? There's no way to predict which will run, is there?

 

[Fazza]

From my reliable Walkenbach "Excel 2000 Power Programming with VBA", "precede the procedure name with the module name and a dot". Such as

Call Module1.MySub
'for routine MySub in module Module1

 

[xenou]

I don't think it would even compile if there was a name conflict. I hope you aren't actually creating subs with the same names in your modules ...

 

[tfield98]

I don't think it would even compile if there was a name conflict. I hope you aren't actually creating subs with the same names in your modules ...

Actually, surprisingly, it does compile! I'm not creating these subs, but I'm using .Copy to copy in some worksheets from another workbook and I wanted to be sure the right Public Subs get called if there are duplicates.

Tom

 

[xenou]

You created a sub called Copy()?

 

[tfield98]

You created a sub called Copy()?

No, sorry for my mis-communication. I'm using VBA's .Copy to copy a sheet from one workbook to another.

Now I just need to find a way to programatically delete code from a sheet. The darned security model won't let me run this code:

Set CodeMod = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule

On Error Resume Next
With CodeMod
.DeleteLines lngStart, .CountOfLines
End With

 

[xenou]

Okay, well that's a very different question from the first one in this thread. I'd recommend you start a new thread.

As far as name conflicts, VBA is pretty easy to work with because each Excel project is by nature "hidden" from others. As long as you aren't using names that are typical VBA or Excel function names you're generally okay. I prefer to use private subs as much as possible, and avoid functions names that sound like inbuilt functions for standard libraries of any kind.

 

[Norie]

Tom

How could you get conflicts in public modules if all you are doing is copying worksheets?

The only code that will get copied when you do that will be the code in the worksheet module.

By the way, if you ensure you have Option Explicit at the top of every module then that might force you do use module references.

Well it'll stop the code compiling anyway.

Still don't see how or why you could end up with potential conflicts though.

Oh, forgot about the last thing - you'll just need to change your security settings manually - I'm pretty sure it can't be done with code.