Problems using Array()

[David C. Holley]

I'm trying to create a procedure that parses out a comma-delimited
string and returns a specific value in it using the values ordinal
position. (ie. grab "test3" out of "test1", "test2", "test3", "test4")

formatOpenArgsString() successfully takes a string and breaks up as
needed placing quotes " before and after each value and maintaining the
commas

getOpenArguementValue() is creating the array, however its not parsing
out the value returned by formatOpenArgsString(). It is taking the
entire value and placing it into the array. The Array() does not appear
to be recognizing the commas returned by formatOpenArgsString()

formatOpenArgsString() in: test1, test2, test3, test4, test5
formatOpenArgsString() out: "test1", "test2", "test3", "test4", "test5"
getOpenArguementValue(0) out: "test1", "test2", "test3", "test4", "test5"

getOpenArguementValue(0) SHOULD be test1
but it is not.

Function getOpenArguementValue(strOpenArgs, intPosition)

Dim openArgsArray As Variant
openArgsArray = Array(formatOpenArgsString(strOpenArgs))

getOpenArguementValue = openArgsArray(intPosition)

End Function

Function formatOpenArgsString(strOpenArgs)

Dim strNewString As String
Dim i As Integer
Dim nextCharacter As String

strNewString = Chr(34)
For i = 1 To Len(strOpenArgs)
nextCharacter = Mid(strOpenArgs, i, 1)
Select Case nextCharacter
Case " "
Case ","
strNewString = strNewString & Chr(34) & ", " & Chr(34)
Case Else
strNewString = strNewString & nextCharacter
End Select
Next i

strNewString = strNewString & Chr(34)
Debug.Print strNewString
formatOpenArgsString = strNewString

End Function

 

[John Nurick]

Hi David,

You're making more of a meal of this than you need - or you've left
something important out of the description. Try this:

strOpenArgs = "test1, test2, test3, test4, test5"
debug.print Split(strOpenArgs)(2)

 

[David C. Holley]

I was not aware that Split() existed as I've never needed something like
until now.

I would still like to know what the underlying problem is with the code
that I posted.

 

[John Nurick]

I would still like to know what the underlying problem is with the code
that I posted.

As Help says, the Array() function takes "a comma-delimited list of
values" and converts it into an array. You were passing it a single
string (and Array() didn't care that the string contained a
comma-and-quote-delimited list of values). IOW the difference between

Array("test1", "test2", "test3") 'works

and

Array("""test1"", ""test2"", ""test3""") 'doesn't work

Split() is the function for splitting strings into arrays. You can use
any delimiter, and it doesn't want quotes.

 

[David C. Holley]

But the return value of the formatOpenArgsfunction() is

"test1", "test2", "test3"

I still don't get why Array() doesn't like the return value of that
function. When it appears to be EXACTLY like the string that Array()
properly splits.

 

[John Nurick]

David, the return value of formatOpenArgsString()is a *single* *string*
*containing*
"test1", "test2", "test3"

What Array() wants is a list of *separate* arguments
"test1"
"test2"
"test3"

Look at the example in Help:
A = Array(10,20,30)
B = A(2) 'Value of B is 30
There are three arguments, integer literals separated by commas. This is
not at all the same thing as a string containing three integer literals
separated by commas:
A = Array("10,20,30")
B = A(2) 'Error - subscript out of range
B = A(0) 'Value of B is "10,20,30"

Where your list of values is in a string, use Split():
A = Split("10,20,30", ",")
B = A(2) 'Value of B is 30

 

[David C. Holley]

The following example is a copy & paste from HELP showing a
comma-delimited list of values

MyWeek = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")

The output from the function supplies the EXACT text as shown in the
example. If the function produces a string with commas separating the
values, Why doesn't Array() recognize the delimiter? Do I need to use Eval()


Below is a copy & paste from the immediate window.
-------------------------------------------------------------------
'Copy & Paste from help
MyWeek = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")
?MyWeek(2)
Wed

'formatOpenArgsString
?formatOpenArgsString("Mon, Tue, Wed, Thu, Fri, Sat, Sun")
strOpenArgs: Mon, Tue, Wed, Thu, Fri, Sat, Sun
strNewString: "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"
"Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"

'Copy & past output from function
MyWeek1 = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")
?MyWeek(2)
Wed

'Put all together
MyWeek2 = Array(formatOpenArgsString("Mon, Tue, Wed, Thu, Fri, Sat, Sun"))
strOpenArgs: Mon, Tue, Wed, Thu, Fri, Sat, Sun
strNewString: "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"
?MyWeek2(0)
"Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"

 

[John Nurick]

For the third and last time,

A string variable CONTAINING a comma-delimited list of values IS NOT THE
SAME THING as a comma-delimited list of values.

Here's an analogy:

A crate of twelve bottles of wine is not the same thing as twelve
bottles of wine - especially if you're trying to open it with a
corkscrew.

The following example is a copy & paste from HELP showing a
comma-delimited list of values

MyWeek = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")

The output from the function supplies the EXACT text as shown in the
example. If the function produces a string with commas separating the
values, Why doesn't Array() recognize the delimiter? Do I need to use Eval()


Below is a copy & paste from the immediate window.
-------------------------------------------------------------------
'Copy & Paste from help
MyWeek = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")
?MyWeek(2)
Wed

'formatOpenArgsString
?formatOpenArgsString("Mon, Tue, Wed, Thu, Fri, Sat, Sun")
strOpenArgs: Mon, Tue, Wed, Thu, Fri, Sat, Sun
strNewString: "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"
"Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"

'Copy & past output from function
MyWeek1 = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun")
?MyWeek(2)
Wed

'Put all together
MyWeek2 = Array(formatOpenArgsString("Mon, Tue, Wed, Thu, Fri, Sat, Sun"))
strOpenArgs: Mon, Tue, Wed, Thu, Fri, Sat, Sun
strNewString: "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"
?MyWeek2(0)
"Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"

 

[David C. Holley]

So is it possible to convert the string variable to the comma-delimited
list?

 

[John Nurick]

If you're sane and working in VB/VBA the answer is no. You use Split()
to split the string directly into an array.

So is it possible to convert the string variable to the comma-delimited
list?

 

[David C. Holley]

I will be using the Split() function. I am looking for an answer so that
I can learn.

(and Again...)
So is it possible to convert the string variable to the comma-delimited
list?

 

[Marshall Barton]

Gee, David, I think you wore John out ;-)

Of course it's possible to convert a comma deliminated
string to its component elements. How do think it was done
prior to the introduction of the Split function ;-)

Just use the InStr function (or even Mid) in a loop, but the
form of the result depends on how you're going to use the
separate items (an array is just a convenient way to hold a
bunch of values).

Don't forget that a string that contains some commas is
still a string, The Array function is just a function with
a list of arguments, which is defined to be a comma
separated list of values.

 

[John Nurick]

Marsh, as I understand it David wants to convert a string-containing-a-
comma-delimited-list-of-values into a comma-delimited list of arguments
for the Array() function.

Obviously there's no problem parsing a string into a *known number* of
elements and passing these , e.g.

strS = "One, Two, Three"
'use any technique you like to parse into str1, str2, str3
...
varArray = Array(str1, str2, str3) '<<<

But I don't see how you can do this in VB/A when you don't know the
number of elements in the list at compile time and therefore don't know
how many arguments to include in the statement marked <<< (short of
writing crazy self-modifying code).

 

[David C. Holley]

You missed what I'm trying to do. I'm trying to do. I want to take the
return value from a function and use it as the parameter in the Array()
function. The function returns a string that APPEARS to be a list of
values separated by commas, however the Array() function is viewing it
as a single string.

 

[Marshall Barton]

You missed what I'm trying to do. I'm trying to do. I want to take the
return value from a function and use it as the parameter in the Array()
function. The function returns a string that APPEARS to be a list of
values separated by commas, however the Array() function is viewing it
as a single string.

OK, in that case, as John said, you can not include
syntactic elements inside a string and expect Array to parse
out the individual values. That's the job of the compiler's
lexical analyzer or a function specifically designed to do
the parsing.

At first glance, the Eval function can do this for you.
Unfortunately, the Array function returns a Variant array
(VarType 8204), which Eval can't deal with, so that's out.

You could use Eval to call your own function, which in turn
could store its argument list's values in an array (and just
return some innocuous scalar value), but what's the point?
If you have to go to all that trouble, you might as well
just do the parsing in your function and be done with it.

 

[John Nurick]

You missed what I'm trying to do. I'm trying to do. I want to take the
return value from a function and use it as the parameter in the Array()
function. The function returns a string that APPEARS to be a list of
values separated by commas, however the Array() function is viewing it as
a single string.

I think it's probably possible to do this by writing a function in C or C++
and compiling into a DLL which can be called from VBA, but I don't want to
go there.

Remember, however, that the purpose of the Array() function is to turn a
list of values into an array. So there's no point busting a gut to write a
function whose return value is the sort of list required by Array(): all you
need do is use or write a function that creates the same array that Array()
would create.

 

[Marshall Barton]

Marsh, as I understand it David wants to convert a string-containing-a-
comma-delimited-list-of-values into a comma-delimited list of arguments
for the Array() function.

Obviously there's no problem parsing a string into a *known number* of
elements and passing these , e.g.

strS = "One, Two, Three"
'use any technique you like to parse into str1, str2, str3
...
varArray = Array(str1, str2, str3) '<<<

But I don't see how you can do this in VB/A when you don't know the
number of elements in the list at compile time and therefore don't know
how many arguments to include in the statement marked <<< (short of
writing crazy self-modifying code).

I guess I have thoroughly stuck my foot into it here ;-)

OTOH, if it requires the use of a procedure to parse out the
values, it might as well just stuff the values into an array
in the usual way. Why mess with the Array function??

Regardless of my irrelevant interjection, the above nicely
summarizes the issue of trying to use syntatic elements in a
string.