"cannot assign values to an array"

[jimliu]

Hi Experts,

When I call a function that returns an array, and try to assign the returned array to a variable, I get an error message saying "cannot assign values to an array".

The following is a simplified version of what try to do, just to highlight the problem:

'1. I create a function called gen_array(s1 as String, s2 as String)
'2. In a test() function I try to call the above gen_array(s1, s2), then the error occurs.

Function gen_array(s1 As String, s2 As String) As String
Dim a(2) As String
a(0) = s1
a(1) = s2
gen_array = a
End Function

Function test()
Dim s(2) As String
s = gen_array("jim", "liu")
MsgBox (s(0))
End Function

Your help is greatly appreciated.

best regards
Jim

[fumei]

Function gen_array(s1 As String, s2 As String) As String
Dim a(2) As String
a(0) = s1
a(1) = s2
gen_array = a
End Function

gen_array is declared as returning a string. Yes?

gen_array = a is NOT a string
gen_array = "a" IS a string

However, this would mean gen_array returns "a". A function returns a single thing. As a function gen_array As String will return a SINGLE string.

Also you are declaring arrays as (2), but you never fully dimension it. a(2) requires a(0), a(1), and a(2). This also makes:
Dim s(2) As String
s = gen_array("jim", "liu")
not work.

[jimliu]

Hi fumei,

Thank you for your reply, but your suggestion won't solve my problem. The return type should be String() rather than String. You are right there. Actually, I tried with String() before, it did not work. And String didn't work either.

As to the dimensioning, Dim a(2) means that a is an array containing two elements. By default, the index number starts at a(0). And by default, those slots not assigned a value returns an empty string. You can try out your suggestion and mine, and you will see it. I nonetheless tried your suggestion. It didn't work, as expected. :-)

I also tried putting "Set" as follows

Set s = gen_array("jim","liu")

didn't work either.

So it is still an unsolved problem, which should be quite easy for those familiar with VBA.

But thank you for your kind help anyway.

best regards
Jim

[TonyJollans]

Hi Jim,

As you say, the return type needs to be String(). The variable you set it to also needs to be String() ...Function gen_array(s1 As String, s2 As String) As String()
Dim a(2) As String
a(0) = s1
a(1) = s2
gen_array = a
End Function

Sub test()
Dim s() As String
s = gen_array("jim", "liu")
MsgBox (s(0))
End Sub

[jimliu]

Hi Tony,

Thank you very much. Your suggestion works. The secret is: when declaring an array to be populated by the array returned from a function, the declaration should not have a number specification. In the test() function, "Dim s() as String" is right, while "Dim s(2) as String" won't work.

By the way, how do you guys manage to have the VBA code color coded in this forum. When I paste from the VB Editor in Office 2003, even the indentation is lost, which makes it quite difficult to read.

Thank you again.

best regards
Jim

[fumei]

Boy I feel dumb....

Use the VBA tags. Put your code between left square bracket ([) and "vba" followed by right square bracket (]); end you code with the same but have the closing tag - as in /vba (but with the brackets.

[jimliu]

Thanks Fumei.