Apostrophes in cell Range Object referencing. Spaces in Name

[Doc.AElstein]

Hi
I just solved a problem by trial and error, while preparing the question .. . ( LM POP :) . .) - But the solution is confusing me.

Can anyone help clarify, or failing that just confirm my findings and conclusions…

This is the situation:
This sort of code line was working fine for me in a Workbook containing a Worksheet with the name “Manual” ( The workbook Name had no spaces in it )

Code: Select all

 Sub ApplicationcellRangeObjectRef()
' Range Object Method cell reference
 Let Application.Range("=[" & ThisWorkbook.Name & "]Manual!A1").Value = "foop"
End Sub

( The code puts a foop in the first cell in Worksheet “Manaul” )

In a similar workbook with a space in the workbook name the code errored ( Run time Error ‘1004’ : The method ‘Range’ for the Object Application failed )
I had read somewhere here something about including Apostrophes… So after a bit of trial and error I found that the following modified code line , ( adding a ' ' Pair ) , will also work for the case of a space in the Workbook Name:

Code: Select all

 
Application.Range("='[" & ThisWorkbook.Name & "]Manual'!A1").Value = "foop"

The reason this is confusing me is the following: I had up until now, thought that the apostrophes were required only for the case of extending a reference to a closed workbook. .. I thought this because without the extra ' ' Pair I cannot get an external reference to work even when all names have no spaces in them _..

_..For example:

The following code lines will work. ( I tested by creating two workbooks, ( “My Closed Workbook.xlsm” and “MyClosedWorkbook.xlsm” ) . The Workbooks are in the same folder as the Workbook in which the code is run )

Code: Select all

 Application.Range("='[" & ThisWorkbook.Name & "]Manual'!A1").Value = "='" & ThisWorkbook.Path & "\" & "[MyClosedWorkbook.xlsm]Manual'!A1"
  Application.Range("='[" & ThisWorkbook.Name & "]Manual'!A1").Value = "='" & ThisWorkbook.Path & "\" & "[My Closed Workbook.xlsm]Manual'!A1"
  Application.Range("='" & ThisWorkbook.Path & "\" & "[" & ThisWorkbook.Name & "]Manual'!A1").Value = "foops"

The following codes, ( where I have omitted the ' ' Pair ) , will not work, even with Workbook Names and Worksheet Names all having no spaces in them

Code: Select all

Application.Range("='[" & ThisWorkbook.Name & "]Manual'!A1").Value = "=" & ThisWorkbook.Path & "\" & "[MyClosedWorkbook.xlsm]Manual!A1" ' Run time Error '1004': Application-defined or Object-defined error
Application.Range("=" & ThisWorkbook.Path & "\" & "[" & ThisWorkbook.Name & "]Manual!A1").Value = "foops" ' Run time Error '1004' : The method 'Range' for the Object Application failed

Hence my ( probably wrong ) conclusion that the ' ' Pair was something to do with a closed workbook reference.
_....

Is the answer simply that in general the inclusion of the ' ' Pair are the correct syntax, and that it is just a case of for the “open” workbook reference VBA “Guesses correctly” and as a result of this “guessing” , Excel includes the ' ' Pair in its “seen compiled” code version.
I note that Excel does do some strange things with references / Links. For example: If I type something like ='Manual'!A1 in a Cell, then Excel removes the ' ' Pair, and I see finally in the Formula Bar =Manual!A1
If , on the other hand, my Worksheet Name has a space in ( say for example “Man u al” and I type something like ='Man u al'!A1 in a Cell, then Excel does not change anything , and I see finally in the Formula Bar exactly what I typed in , ='Man u al'!A1

( I note also as an aside, just in passing, that a closed workbook reference is changed by Excel to an open workbook reference if you “open” the workbook referenced in the original closed workbook reference
For example this_..
='H:\Excel0202015Jan2016\OffenFragensForums\RCA1JorgeAllenJan\[MyClosedWorkbook.xlsm]Manual'!A1
_..will change to this if the closed Workbook is opened
=[MyClosedWorkbook.xlsm]Manual!A1
Or, this_..
='H:\Excel0202015Jan2016\OffenFragensForums\RCA1JorgeAllenJan\[My Closed Workbook.xlsm]Manual'!A1
_..will change to this if the closed Workbook is opened
='[My Closed Workbook.xlsm]Manual'!A1
- But I have a theory about that one which I am generally happy with, - Once again just the disappearing ' ' Pair in the first example here is a bit strange )


So does anyone have a clear definition or understanding of this ' ' Pair thingy and / or can explain what is going on in the strange behavior where Excel changes these reference strings. I have not been able to find any clear documentation on this

It is not a big problem. For now I will just routinely add the ' ' Pair and let VBA or Excel choose to remove them if it so desires ! But I just wanted to check if I am missing some important point here.
Thanks
Alan

[HansV]

The general rule for a reference to a range on another worksheet is: if the part before the ! contains spaces or punctuation, it MUST be enclosed in apostrophes.
Even if apostrophes are not required, it doesn't hurt to include them; Excel will automatically omit them if possible.
So, as you note yourself, it's safest to ALWAYS include the apostrophes in VBA code.

[Doc.AElstein]

Hi Hans,
Thanks for that confirmation. It is annoying that these things are not clearly documented anywhere, ( at least I could not find it ).
_..

… Excel will automatically omit them if possible….

In the case of a closed Workbook reference , Excel not only does not omit them , but seems to always require them, even when there are no spaces … possibly this is covered by what you said here:

…if the part before the ! contains ….. or punctuation, it MUST be enclosed in apostrophes..

It seems that Excel is possibly regarding a Backslash as a punctuation, but not necessarily regarding the square brackets as punctuation.

These sort of anomalies in string building seem to crop up in Excel.

Thanks for the reply

Alan