Excel vba syntax query

  Chris the Ancient 20:09 17 Nov 2003

Thanks to the various kind people who have been helping me along these last few weeks. With all the packages I'm developing, I'm quite close to a final version.

Because I cannot rely on the end user not changing spreadsheet names and locations, and I have various macro calls that need to remember what the package name is, I'd like a 'volatile' package handling routine.

I've got as far as using ThisWorkbook.Path and ThisWorkbook.Name quite successfully and can store them as a public variables with no problem.

Where I hit a snag is working out the way of using this variable in an Application.Run routine.

How do I change the statement...


to use a variable declared as BookName instead of TestBook.xls? I get lost with the quotation marks!



  VoG II 20:23 17 Nov 2003

Short of time at the moment Chris so haven't tried this but

Application.Run BookName & "!MacroName"

might work.

I'll check back later.

  Chris the Ancient 20:32 17 Nov 2003

Knew you'd be out there somewhere!

Did try that and double-checked following your response. 'fraid not.


Compile Error:

Expected: end of statement

I get the feeling that there needs to be a string building thingy that seems to shuffle the quote marks about.

But then, what do I know?

I shall also continue playing.


  Chris the Ancient 21:04 17 Nov 2003

I think I've cracked it.

I made a variable named RunMe that 'de-compiled' the variable I called BookName.

Then, very slowly - and carefully - build a string statement that looked like I wanted.

RunMe = BookName


ActiveCell = BookName


ActiveCell = RunMe


ActiveCell = """" & RunMe & "!MacroName"""

The intermediate 'messages' I've kept in to show how I got there. All I can say is, "By 'eck, there's some darned queer quote mark configurations!"

I shall now try it on the real thing!


  Chris the Ancient 21:05 17 Nov 2003

There should be a line space before each of the Range statements.

  pc moron 21:11 17 Nov 2003

Is this what you mean:

Sub Test()

Dim BookName As String
BookName = ThisWorkbook.Name
Application.Run (BookName & "!" & "ThisMacro")

End Sub

Sub ThisMacro()

Worksheets(1).Cells(1, 1).Value = "ThisMacro has run"

End Sub

The above works okay.

  Chris the Ancient 21:22 17 Nov 2003

Now that is a MUCH tidier and neater way of doing it (dammit - I thought I had done so well!)

An interesting way of using brackets that I'd not seen before. I shall have to remember that one. Ve-e-e-e-ry useful tip.

And you beat VoG!

MegaThanks and ticked and closed.


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best Amazon Echo: What’s the best Alexa speaker?

Kano Computer Kit Complete review: A fun DIY 'laptop' that teaches kids to code

Best pro photo editors for Mac 2018

TV & streaming : comment regarder les Jeux olympiques d’hiver 2018 ?