Evaluate string to join 1D array

[YasserKhalil]

Hello everyone
I have a UDF that generates a string like that

Code: Select all

Join(Array(Chr(199), Chr(225), Chr(205), Chr(207), Chr(237), Chr(203)), Empty)

This string should be evaluated but when using Evaluate I got Error 2029
How can this string be evaluated?

The result should be like the result of this line

Code: Select all

Debug.Print Join(Array(Chr(199), Chr(225), Chr(205), Chr(207), Chr(237), Chr(203)), Empty)

Posted here too
https://www.mrexcel.com/board/threads/e ... y.1140921/
https://stackoverflow.com/questions/63056790/

[HansV]

I created this function to test it:

Code: Select all

Function Test()
    Test = Join(Array(Chr(199), Chr(225), Chr(205), Chr(207), Chr(237), Chr(203)), Empty)
End Function

When used in a cell formula, this is the result:

S3431.png

So the expression is valid.

[Doc.AElstein]

The string that your code line produces, looks sensible, ( In a manner of speaking ) , to me also...
I am not quite sure what you are asking, but The Evaluate( ) takes a string argument
If you just put Join(Array(Chr(199), Chr(225), Chr(205), Chr(207), Chr(237), Chr(203)), Empty) in it, then it is going to be looking for a worksheet thing that looks like the text that your Join(Array(Chr(199), Chr(225), Chr(205), Chr(207), Chr(237), Chr(203)), Empty) creates.
Of course it wont find anything with that name
A string inside Evaluate( ) itself , will need to have an extra quote each side, ....
I think you have had this issue a few times already...
The string to evalute must look sensible to Evaluate - it must look like a string, so you really should by now be in the habit of looking at the string you have, before you put it into Evaluate, to make sure it looks like what you want Evaluate to see

Code: Select all

Dim v
 v = Join(Array(Chr(199), Chr(225), Chr(205), Chr(207), Chr(237), Chr(203)), Empty)
Dim strEval As String
 Let strEval = """" & Join(Array(Chr(199), Chr(225), Chr(205), Chr(207), Chr(237), Chr(203)), Empty) & """"
Debug.Print strEval
 v = Evaluate("""" & Join(Array(Chr(199), Chr(225), Chr(205), Chr(207), Chr(237), Chr(203)), Empty) & """")
End Sub

You don't want Evaluate to see that
Callie
stuff
You want it to see
"Callie"

[YasserKhalil]

Thank you very much for great solutins.
So using extra quote on each side solve this problem
I tested that but doesn't work on my case
Here's the UDF that generated the string

Code: Select all

Sub Test_ConvertToUnicode_UDF()
    Dim s
    s = ConvertToUnicode("الحديث")
    Debug.Print Evaluate("""" & s & """")
End Sub

Function ConvertToUnicode(ByVal sInput As String)
    Dim s As String, i As Long
    For i = 1 To Len(sInput)
        s = s & "Chr(" & Asc(Mid(sInput, i, 1)) & ")" & IIf(i <> Len(sInput), ", ", Empty)
    Next i
    ConvertToUnicode = "Join(Array(" & s & "), Empty)"
End Function

[Doc.AElstein]

I am not surre what you are trying to do, but, your function returns a string.
So Evaluate("""" & s & """") is correct and returns the string
Join(Array(Chr(199), Chr(225), Chr(205), Chr(207), Chr(237), Chr(203)), Empty)

Possibly you want to atually get that VBA command
Join(Array(Chr(199), Chr(225), Chr(205), Chr(207), Chr(237), Chr(203)), Empty)
to be done.

I am not sure how to do that simply, sorry. Possibly there is a way that I don't know about. Maybe someone else does..
I geuss you want something like a pseudo INDIRECT function that takes the string of the command you want

[YasserKhalil]

Yes this part is the right question
Possibly you want to atually get that VBA command
Join(Array(Chr(199), Chr(225), Chr(205), Chr(207), Chr(237), Chr(203)), Empty)
to be done.

[LisaGreen]

My two peneth...

I personally prefer to define a string and set it to double quotes rather than using so many multiple double quotes. For me it just makes the strings easier to read/build.

Dim slDQ as string

slDQ = Chr(34)

Lisa

[Doc.AElstein]

Hi Lisa
Yes, I have noticed more recently a lot of people using that alternative to the more complex multi quote syntax.
But it took me so many years to finally master the multi quote syntax , that I got quite fond of them in the meantime, and I can produce some very long code lines with sometimes many 8-10 sequential quote sections, and they can look quite beautiful as one scrolls along them, :)

By the way, the only thing I could think of to solve Yasser’s outstanding issue, was to write a macro to make a macro which had that code line of his in it.
So he would need to write a function , or include in his existing function a section, to make a new procedure which includes his code line string, then get the same procedure to execute that procedure to get the results.
One thing to be careful there, is that such things often seem to have bugs which result in nasty crashes. It would probably be wise to split the making and running of the procedure to be done by separate macros which schedule the running of the next procedure. The time scheduled can even be immediately after the procedure scheduling it, ( which means you schedule it for Now)
In theory this may seem a pointless exercise. But in the practice it often seems an effective workaround to the nasty crashes that such things seem to cause….


Alan

[YasserKhalil]

Thanks a lot Mr. Alan for the reply.