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 Black Friday Deals 2017

Black Friday Deals for Designers & Artists: Adobe, Apple, Corel Painter, Microsoft Surface, Wacom &e…

Best Black Friday Apple Deals 2017

Black Friday 2017 : date, sites participants & bonnes affaires