Excel vba syntax query

  [DELETED] 20:09 17 Nov 2003
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

  [DELETED] 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.

  [DELETED] 20:32 17 Nov 2003

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

  [DELETED] 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

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

  [DELETED] 21:05 17 Nov 2003

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

  [DELETED] 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.

  [DELETED] 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.

Chris

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

Elsewhere on IDG sites

Microsoft Surface Book 2 15in review

Illustrator Amy Grimes on how setting up her own eco-brand led to success with clients too

MacBook Pro keyboard issues and other problems

Test : l’enceinte connectée HomePod d’Apple