Solved: Call sub on active worksheet using macro on Personal Macro Workbook

[hkeiner]

My objective is to create a macro (PasteTokenData_M) on the Personal Macros Workbook that can run subs (e.g., ReplaceTokens) existing in a module (mPointLinksFieldsXls) on various worksheets. I will also add a QAT icon that will run this macro when a particular worksheet containing the subs is active. I have not been able to make this work. I get an error message on the Application.Run command. (see attached)

A simplified version of my macro code is below. Is there an obvious mistake in my code? Perhaps it is in how I am using the "GetFileName" variable to call the subs on the active worksheet. .


[vba]
Option Explicit
Public Sub PasteTokenData_M()
'
'
'DETERMINE NAME OF ACTIVE DOCUMENT
Dim GetFileName As String
GetFileName = ActiveWorkbook.Name

'RUN CODE ON ACTIVE WORKSHEET TO REPLACE TOKENS
Application.Run "GetFileName!ReplaceTokens"

End Sub
[/vba]

[Simon Lloyd]

I've not tested it nor have the time to test it but it may be this line[VBA]Application.Run "GetFileName!ReplaceTokens"
[/VBA]should perhaps be[VBA]Application.Run GetFileName & "!ReplaceTokens"
[/VBA]

[Bob Phillips]

And you will need single quotes if the filename has embedded spaces.

[hkeiner]

[vba]
Application.Run GetFileName & "!ReplaceTokens"
[/vba]

The above change did the trick. It also seems to work OK when the file names have spaces. Thanks for the help.

I am just starting to use VBA with Excel and I am gueessing that learing some of the common tricks (pitfalls) will help me avoid a lot of frustration. For example,



1) When and why does the GetFileName variable and the sub name have to be separated by a "&" when using the Application.Run command?

2) Exactly how would I use the 'single quote' method if spaces in file names exist and do become a problem. Would it be as follows:

[vba]
Application.Run 'GetFileName' & "!ReplaceTokens"
[/vba]

[Bob Phillips]

1) When and why does the GetFileName variable and the sub name have to be separated by a "&" when using the Application.Run command?

When you need to concatenate a variable and a text value.

2) Exactly how would I use the 'single quote' method if spaces in file names exist and do become a problem. Would it be as follows:

[vba]
Application.Run 'GetFileName' & "!ReplaceTokens"
[/vba]

No

[vba]
Application.Run "'" & GetFileName & "'!ReplaceTokens"
[/vba]

[mdmackillop]

GetFileName is a string, so & joins two strings into the one required to define the macro.

Single Quotes as follows
[VBA]

Application.Run "'" & GetFileName & "'!ReplaceTokens"
[/VBA]

[hkeiner]

Thanks guys.

I am just an accounting manager dabbling with VBA trying to develop some Excel 2007 and Word 2007 tools for my staff to use. I can see that this tip (pitfall) is something I must know and understand if I am going to get much done using VBA macros in Excel 2007. I didn't seem to have this same problem when dabbling with similar VBA macro code in Word 2007. Of course I used "Document" instead of "String" when defining the variable in Word 2007, as shown below. I am guessing that is the reason for the difference and that VBA in Excel 2007 does not have the "Document" type or something similar.

[vba]
Dim GetFileName as Document
[/vba]

[Bob Phillips]

In Excel, you can use Workbook

[vba]
Dim GetFilename As Workbook
[/vba]

and use the name property

[vba]

Application.Run "'" & GetFileName.Name & "'!ReplaceTokens"
[/vba]

but you still need the single quotes if the name has embedded spaces.

I haven't tested it here, but I would expect it to be the same in Word.

[hkeiner]

You information has been gold for me.

I just bought myself "VBA for Dummies Excel 2007" and am plowing though it. My intent is not to become a skilled VBA programmer (my real love is accounting not programming) but I realize it is not practical (or appropriate ) to use this forum to learn the basics. I'll save my forum questions for when I am stumped after learning more of the basics.

Thanks again.

[Simon Lloyd]

The good folk here have no problem answering any of your questions, no matter how daft they sound to you! - we all started somewhere (in fact sometimes i feel like im still in the starting blocks )