Menu
Application.Run "MyWorkBook.xls!Macro1"
will work (run the macro called Macro1 in the MyWorkBook.xls file).
Application.Run "My Work Book.xls!Macro1"
will not work (if the workbook name contains spaces, Excel says "the macro cannot be found").
I just spent hours figuring out that it's the spaces causing this problem...
Is there a way around this without renaming the file?
489
To remove additional or extra spaces we use different VBA functions like LTRIM, TRIM, or RTRIM. We use LTRIM function to remove left most or starting of the string spaces. To remove right most or end of the string spaces we use RTRIM function.
Using trim (or trim$) in VBA will remove the leading and trailing spaces, which as mentioned is different from =TRIM in Excel. If you need to remove spaces (as mentioned below not necessarily all whitespace) from inside a string simply use WorksheetFunction. Trim . Save this answer.
The VBA SPACE function is listed under the text category of VBA functions. When you use it in a VBA code, it returns a string which is consisting of a specified number of spaces. In simple words, with space function, you can create spaces by supplying numbers.
You can cause Excel to run a macro automatically whenever a particular workbook is closed. For instance, when the workbook is closed you might want to run a macro that asks the users if they want to perform some task, such as saving the day's data to another file.
Use single quotes surrounding the filename:
Application.Run "'My Work Book.xls'!Macro1"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
