Using Application.Run to call a private sub in a module from ThisWorkbook object

Hi all,

Banging my head. I can't work out why this is not working.

I have the following code behind the "ThisWorkbook" object

Please Login or Register  to view this content.

and some example code in sit in a normal module called SubsToCall

Please Login or Register  to view this content.

However, when running the event Workbook_Open as soon as it hits the first instance of .Run I get a '1004' runtime error "Application-defined or object-defined error".

Why can I not 'Run' a sub from the ThisWorkbook object? I can 'Call' public and normal subs, but not private, and I'm fairly sure that I have used 'Run' from a sheet object before. Any ideas?

You cannot call a private sub outside it's module. for the rest goes.

Please Login or Register  to view this content.

Hi,

Your code works for me, as expected. Is the workbook opening in Protected mode perhaps, and then being allowed? That causes many problems with Application calls, though usually the message is something like 'Method Run of the Application class failed.'

Originally Posted by bakerman2

You cannot call a private sub outside it's module

You can with Run, actually- it ignores scope.

The error arises when the name of the module is spelled wrong.
So check the spelling of the module name in your WorkBook_Open.

Originally Posted by Logit

Usually, macros are called from ThisWorkbook in this manner

Originally Posted by bakerman2

for the rest goes.

Hi, I've already stated that 'Call' works (with or without the 'Call' method) for public and normal subs.

Originally Posted by xlnitwit

Hi,

Your code works for me, as expected. Is the workbook opening in Protected mode perhaps, and then being allowed? That causes many problems with Application calls, though usually the message is something like 'Method Run of the Application class failed.'

No workbook protection. I unprotected every sheet in the workbook but got the same result. Thanks for testing for me.

Originally Posted by bakerman2

The error arises when the name of the module is spelled wrong.
So check the spelling of the module name in your WorkBook_Open.

That was my first thought, so I have already double checked the spelling and copy/pasted the module name into the .Run parameter.

However, to eliminate the module name / spelling as a factor I renamed the module to "m" and it worked as expected. I then tried to following to find out what was throwing a spanner in the works:

Please Login or Register  to view this content.

So it seems that the keyword "sub" is being parsed by the .Run method in a certain (unexpected in this case) way. I've renamed my module to "ProcsToCall" and everything works as expected. I use the module name "SubsToCall" quite a lot so I will have to remember to change all of them to avoid this error in the future.

Thanks everybody for all your help moving towards the solution, I appreciate it!

Glad you got it working. Thanks for rep+.

Originally Posted by Stormin'

So it seems that the keyword "sub" is being parsed by the .Run method in a certain (unexpected in this case) way.

That is very strange indeed. It works fine here with Excel 2010. Purely out of interest, does it work adding the workbook name thus

Please Login or Register  to view this content.

Thank you for the rep.