Evaluate for external reference

[YasserKhalil]

Hello everyone
I have strange case with Evaluate
I have a code that works fine only if the name of the workbook was short (ef.xlsm) as an example
But the same code throws an error when the name of the workbook is long
and the error occurs at this part of Evaluate statement

Code: Select all

Evaluate("TRANSPOSE(IFERROR(IF((" & rSchool.Address(, , , True)

How can I use True just to refer to the worksheet without the workbook name ... so as to avoid this error?

[HansV]

Code: Select all

    Dim s As String
    Dim p1 As Long
    Dim p2 As Long
    s = rSchool.Address(, , , True)
    p1 = InStr(s, "[")
    p2 = InStr(s, "]")
    s = Left(s, p1 - 1) & Mid(s, p2 + 1)
    ... = Evaluate("TRANSPOSE(IFERROR(IF((" & s & ...)

[Doc.AElstein]

Hi Yasser

I do not really understand what you want to do??
I think it is better if you give whole formula, and try to explain more fully.. it is difficult to know if you want
address ... like
A1
or address string like
"A1"
Without full formula it is difficult to know???

If gave up because I cannot understand question??

If what Hans has done is what you want, then here is another way to do similar…

Code: Select all

Sub TraschWkBk() ' https://www.eileenslounge.com/viewtopic.php?f=30&t=31784
 Debug.Print Range("A1").Address(, , , External:=True) ' or rSchool.Address(, , , True)
Dim strEval As String
 Let strEval = "=RIGHT(""" & Range("A1").Address(, , , External:=True) & """,LEN(""" & Range("A1").Address(, , , External:=True) & """)-SEARCH(""]"",""" & Range("A1").Address(, , , External:=True) & """))"
 Debug.Print strEval
 Debug.Print Evaluate(strEval)
End Sub

Alan

[YasserKhalil]

Thanks a lot Mr. Hans and Mr. Alan for great help.

I have got what you mean by the offered code but how can I use replace instead as I have many parts with the same problem so I am trying to make it simpler
I tried to use such line

Code: Select all

s1 = Replace(rSchool.Address(, , , True), "[" & "*" & "]", "")

I mean to replace any between the brackets "[" and "]" in one shot

[YasserKhalil]

I have tried this UDF to get the address of the sheet name and range only without the full address

Code: Select all

Function GetAddress(ByVal rng As Range) As String
    GetAddress = "'" & rng.Parent.Name & "'!" & rng.Address(External:=False)
End Function

and it worked well but I welcome any suggestions for the improvement

[Doc.AElstein]

Hi Yasser
I do not think that Replace will work with wildcards
One approach could be to get first what you want to replace with nothing, that is to say Get at like “[Book.xls]”
Then replace all of those with “”

Alan
_._______________

Code: Select all

 '    http://www.eileenslounge.com/viewtopic.php?f=27&t=31274&p=242363&hilit=rory#p242363
Sub UseReplaceToWackWkBkrefInExtRefs()
' test string with a couple of full refs in it
Dim rSchool As Range: Set rSchool = Range("A1")
Dim strRefs As String: Let strRefs = rSchool.Address(, , , External:=True) & "Hello Yasser" & rSchool.Address(, , , External:=True)
 Debug.Print strRefs
' Get at all to replace,   like we want  "[Book.xls]"
Dim Pos1 As Long, Pos2 As Long, BitToTrasch As String
 Let Pos1 = InStr(strRefs, "["): Let Pos2 = InStr(strRefs, "]")
 Let BitToTrasch = Mid(strRefs, Pos1, Pos2 - Pos1 + 1)
 Debug.Print BitToTrasch
 Let BitToTrasch = Mid(strRefs, InStr(strRefs, "["), InStr(strRefs, "]") - InStr(strRefs, "[") + 1)
' use replace on whole string
Dim UnlyWitShtAdrs As String
 Let UnlyWitShtAdrs = Replace(strRefs, BitToTrasch, "", 1, -1, vbBinaryCompare)
 Debug.Print UnlyWitShtAdrs
 Let UnlyWitShtAdrs = Replace(strRefs, Mid(strRefs, InStr(strRefs, "["), InStr(strRefs, "]") - InStr(strRefs, "[") + 1), "", 1, -1, vbBinaryCompare)
 Debug.Print UnlyWitShtAdrs
End Sub

Ref
http://www.exceluser.com/excel_help/fun ... dcards.htm" onclick="window.open(this.href);return false;
http://www.eileenslounge.com/viewtopic. ... ry#p242363" onclick="window.open(this.href);return false;
https://www.excelcampus.com/vba/vba-imm ... dow-excel/" onclick="window.open(this.href);return false;

[HansV]

You could set up a Regular Expression object at the start of the code:

Code: Select all

    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "\[.*?\]"

You can then use

Code: Select all

    s1 = re.Replace(rSchool.Address(External:=True), "")

[Doc.AElstein]

I do not think that Replace will work with wildcards

_.. or so I thought...

You could set up a Regular Expression object ....

_.. and that works if the references are different as well. A very handy code snippet to know about ! , thanks Hans.. I was wondering how to do that. That is wild, like a wild thing http://www.youtuberepeater.com/watch?v=Hce74cEAAaE" onclick="window.open(this.href);return false;

Code: Select all

 Sub UseRegsReplaceToWackWkBkrefInExtRefs()
' test string with a couple of full refs in it, ( Have any two workbooks open for this demo to work )
Dim rSchool As Range: Set rSchool = Workbooks.Item(1).Worksheets.Item(1).Range("A1")
Dim rSchule As Range: Set rSchule = Workbooks.Item(2).Worksheets.Item(1).Range("A1")
Dim strRefs As String: Let strRefs = rSchool.Address(, , , External:=True) & "Hello Yasser" & rSchule.Address(, , , External:=True)
 Debug.Print strRefs
'   http://www.eileenslounge.com/viewtopic.php?f=30&t=31784&p=246105#p246105
Dim Reg As Object
Set Reg = CreateObject("VBScript.RegExp")
 Let Reg.Pattern = "\[.*?\]"
' use Reg's  Replace  on whole string
Dim UnlyWitShtAdrs As String
 Let UnlyWitShtAdrs = Reg.Replace(strRefs, "")
 Debug.Print UnlyWitShtAdrs
End Sub

[YasserKhalil]

Thank you very much for all this awesome help
My Kind Regards for both of you