-
1 Attachment(s)
Following on from the last post , and Preparing an answer for here:
http://www.vbaexpress.com/forum/show...l=1#post426154
http://www.vbaexpress.com/forum/show...l=1#post426155
, while I am waiting to get access to the forum
So, I had not been as thorough as I thought some time ago when I looked at the syntax of things like App.Run, App.OnTime, with and without path strings, etc.
I had another look/ revision:
The TLDR
_1 As you say, (Paul_Hossler ) on the RHS of the ! you will never need bracketing with ' s, (because it is a macro name that cannot have spaces)
_2 On the LHS of the ! you will need bracketing with ' s in two situations
_2a) If the workbook name has a space or more in it
_2b) if you are using a full path, regardless of whether the entire string has any spaces or not
A bit more detail
The ' thing is one of those things in VBA that seems to sometimes serve the purposes of being there and not being there, or being invisible but there to do something, or said in some other better way I don’t know as I am not a computer profi…
There are a few occasions when a space in a name throws a spanner in the works, and bracketing that name or string in a ' pair seems to save the day. (In those situations, as far as I have seen, if you have no space but still have the bracketing with ' s, then it does no harm – they are ignored – they seem then to do nothing as if they were not there)
But once in a while the syntax will insist on that bracketing with ' s regardless of whether you have a space.
So I took another look at my solution suggestion
I still have the file
FileToRunMacroInOtherFile.xls, which has the Application.Run code line(s) in it to run the macro that is in the other file that I still have,
OtherFileWithSubMakeFinalVendorsInIt.xls
But I have now another identical file to that latter one, but just with a slightly different name
OtherFileWithSubMakeFinalVendorsInIt and a space or two or ten in the name.xls
Finally to keep it a bit simpler with hard coding paths I put all the files on my desktop which is at
C:\Users\Elston\Desktop
( and note: I do not have any spaces in that path….)
This macro below has a couple of sections in it.
_In section Rem1 all files must be open. Along the lines of what Paul_Hossler said, you need bracketed ' s only if the file name has a space, or two, or ten etc in it
_ In section Rem 2 you will only need the file FileToRunMacroInOtherFile.xls open. (The others can be open or closed). In this case you seem to need the bracketed ' s even when there is no spaces anywhere.
I think I have seen this phenomena before when messing with path strings - sometimes the syntax insists on the bracketed ' s
Alan
Macro in Modul1 in FileToRunMacroInOtherFile.xls
Code:
Sub AppRunSubMakeFinalVendorsInOtherFile() ' http://www.vbaexpress.com/forum/showthread.php?72087#post426155
Rem 1 all workbooks must be open
'_1a no need for bracketing in 's
Application.Run macro:="" & "OtherFileWithSubMakeFinalVendorsInIt.xls" & "" & "!Modul1.MakeFinalVendors"
Application.Run macro:="" & "OtherFileWithSubMakeFinalVendorsInIt.xls" & "" & "!MakeFinalVendors"
'-1b need bracketing in 's
' Application.Run macro:="" & "OtherFileWithSubMakeFinalVendorsInIt and a space or two or ten in the name.xls" & "" & "!MakeFinalVendors" ' This will error
Application.Run macro:="'" & "OtherFileWithSubMakeFinalVendorsInIt and a space or two or ten in the name.xls" & "'" & "!MakeFinalVendors"
Rem 2 This Workbook, FileToRunMacroInOtherFile.xls must be open, the other two can be open or closed as you choose
' Application.Run macro:="" & ThisWorkbook.Path & "\" & "OtherFileWithSubMakeFinalVendorsInIt.xls" & "" & "!'Modul1.MakeFinalVendors'" ' This will error
Application.Run macro:="'" & ThisWorkbook.Path & "\" & "OtherFileWithSubMakeFinalVendorsInIt.xls" & "'" & "!'Modul1.MakeFinalVendors'"
' Application.Run macro:="" & "C:\Users\Elston\Desktop" & "\" & "OtherFileWithSubMakeFinalVendorsInIt.xls" & "" & "!'Modul1.MakeFinalVendors'" ' This will error
Application.Run macro:="'" & "C:\Users\Elston\Desktop" & "\" & "OtherFileWithSubMakeFinalVendorsInIt.xls" & "'" & "!'Modul1.MakeFinalVendors'", Arg1:="You"
End Sub
Macro in other files
Code:
Option Explicit
Sub MakeFinalVendors(Optional Txt As String) ' Modul1.MakeFinalVendors
MsgBox prompt:="Hello " & Txt
End Sub
FileToRunMacroInOtherFile.xls https://app.box.com/s/vavxf4zmawyf8xgd84mxsrp0940rgezp
OtherFileWithSubMakeFinalVendorsInIt and a space or two or ten in the name.xls https://app.box.com/s/4c99p0kanvd5dbcvofrv6vsqka5y6i2t
OtherFileWithSubMakeFinalVendorsInIt.xls https://app.box.com/s/59tcn65sd5woekc259ujkml60inqmo0k
-
1 Attachment(s)
Coding for this forum main post
https://forum.ozgrid.com/forum/index...ok-codemodule/
https://forum.ozgrid.com/forum/index...46#post1263146
Code:
Option Explicit '
' Experiment 1 Conventional coding
Sub Msg1()
MsgBox prompt:="Exp 1: Hello Ozgrids, ...."
MsgBox prompt:=".... and Goodbye. (Exp 1)"
End Sub
' Experiment 2 Split a procedure by scheduling, (using Application.OnTime) (No delays)
Sub Msg2()
Application.OnTime Earliesttime:=Now, Procedure:="'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'" & "!'Module1.Msg2a ""Hello Ozgrids, ...."" , 2 ' "
End Sub
Sub Msg2a(ByVal Msg As String, ByVal ExpNo As Long)
MsgBox prompt:="Exp " & ExpNo & ": " & Msg & ""
Let Msg = ".... and Goodbye."
Application.OnTime Earliesttime:=Now, Procedure:="'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'" & "!'Module1.Msg2b 2 , """ & Msg & """ ' "
End Sub
Sub Msg2b(ByVal ExpNo As Long, ByVal Msg As String)
MsgBox prompt:="" & Msg & " (Exp " & ExpNo & ")"
End Sub
' ' Experiment 3 Split a procedure by scheduling, (using Application.OnTime) (and a few delays along the way)
Sub Msg3()
Application.OnTime Earliesttime:=Now + TimeValue("00:00:03"), Procedure:="'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'" & "!'Module1.Msg3a ""Hello Ozgrids, ...."" , 3 ' "
End Sub
Sub Msg3a(ByVal Msg As String, ByVal ExpNo As Long)
MsgBox prompt:="Exp " & ExpNo & ": " & Msg & ""
Let Msg = ".... and Goodbye."
Application.OnTime Earliesttime:=Now + TimeValue("00:00:03"), Procedure:="'" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & "'" & "!'Module1.Msg3b 3 , """ & Msg & """ ' "
End Sub
Sub Msg3b(ByVal ExpNo As Long, ByVal Msg As String)
MsgBox prompt:="" & Msg & " (Exp " & ExpNo & ")"
End Sub
