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.

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Hands-on with the Star Wars fighting drones you can fly yourself

iPhone 9 and beyond: 32 amazing future smartphone developments - graphene, supercapacitor…