Excel vba syntax query

  Chris the Ancient 20:09 17 Nov 03
Locked

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...

Application.Run"TestBook.xls!MacroName"

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

TIA

Chris

  VoG II 20:23 17 Nov 03

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 03

Knew you'd be out there somewhere!

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

Gives...

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

  Chris the Ancient 21:04 17 Nov 03

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

Range("B9").Select


ActiveCell = BookName

Range("B10").Select

ActiveCell = RunMe

Range("B11").Select

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

  Chris the Ancient 21:05 17 Nov 03

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

  pc moron 21:11 17 Nov 03

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 03

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.

Chris

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

Hands-on: Acer Predator Triton 700 review

D&AD Awards 2017: see the best design, advertising, illustration, animation and VR of the past year

How to lose weight with an Apple Watch