Array Of Variables

[kbsudhir]

Hi All,

I have a question here.

Can we create a variable which can store array in it..??
Ex. I have three arrays like abc(), art() & swt()
Can I store them in a variable & call them when I require them

Sudhir

[Bob Phillips]

Yes, try it for yourself.

[CreganTur]

You could take a look at this thread where I use an array. It might help you to see an example in action.

[kbsudhir]

I have 16 Textboxes in my Form, i.e TxtBox1 till TxtBox16. Data entered in each textbox is separated by each word & entered into respective arrays.
Ex. TxtBox1 data is separated into each word & entered into array ModelNo(). This process is continued for all the textboxes & only change in the code for each textbox will be array name.

The sample code is given below:
[VBA]
Data = TextBox2.Value

Data = Replace(Data, ",", ", ")
Data = Replace(Data, " ", " ")

DataLen = Len(Data)
StrLen = 0
StrPos = 1
ctr = 0

Do Until StrLen >= DataLen

ReDim Preserve Prefix(ctr)
Prefix(ctr) = ""

Pos = InStr(StrPos, Data, ",", vbTextCompare)

If Pos = 0 Then
GtData = Data

For cnt = 0 To UBound(Prefix)

GtData = Replace(GtData, Prefix(cnt), "")
GtData = Trim(Replace(GtData, ", ", ""))
Next cnt

lngt = Len(GtData)
StrLen = StrLen + lngt

GoTo i

End If

GtData = Mid(Data, 1, Pos + 1)

GtData = Trim(Replace(GtData, ", ", ""))

If ctr = 1 And Pos > 0 Then

GtData = Replace(GtData, Prefix(ctr - 1), "")

Else

For cnt = 0 To UBound(Prefix)

GtData = Replace(GtData, Prefix(cnt), "")
GtData = Trim(Replace(GtData, ", ", ""))

Next cnt

End If

StrLen = Pos + 1
i:
StrPos = StrLen

Prefix(ctr) = GtData

If Prefix(ctr) = "" Then
ReDim Preserve Prefix(ctr - 1)
End If
ctr = ctr + 1

Loop
[/VBA]

The problem is I have to repeat the code for each textbox making only one necessary change that is in the array name. This is making the code too long as most of the code is repetitive.
I want to know is there any way to change the array name dynamically when the textbox name gets changed.
Ex.

crt = 1
Do until crt =>14

Data = TxtBox&crt.value

If Trim(Data)<>”” then
Enter Code
End if

Crt = crt+1
Loop

Please Guide

Thanks
Sudhir

[Bob Phillips]

Put in a separate parameterised procedure and call that fir each textbox.

Wouldn't it be simpler to use Split to break the textbox value into an array?

[kbsudhir]

Thanks XLD

I will try it & let you know

Thanks
Sudhir

[kbsudhir]

I created a function:

[VBA]
Function GetData(ByVal StrData As Variant) As Variant
Dim strLen, dataLen, Pos, cnt, ctr, strPos, lngt As Integer
Dim GtData, Data, Mydata() As String
Data = StrData
dataLen = Len(Data)
strLen = 0
strPos = 1
ctr = 0
Do Until strLen >= dataLen

ReDim Preserve Mydata(ctr)
Mydata(ctr) = ""

Pos = InStr(strPos, Data, ",", vbTextCompare)

If Pos = 0 Then
GtData = Data

For cnt = 0 To UBound(Mydata)

GtData = Replace(GtData, Mydata(cnt), "")
GtData = Trim(Replace(GtData, ", ", ""))
Next cnt

lngt = Len(GtData)
strLen = strLen + lngt

GoTo i

End If

GtData = Mid(Data, 1, Pos + 1)

GtData = Trim(Replace(GtData, ", ", ""))

If ctr = 1 And Pos > 0 Then

GtData = Replace(GtData, Mydata(ctr - 1), "")

Else

For cnt = 0 To UBound(Mydata)

GtData = Replace(GtData, Mydata(cnt), "")
GtData = Trim(Replace(GtData, ", ", ""))

Next cnt

End If

strLen = Pos + 1
i:
strPos = strLen

Mydata(ctr) = GtData

If Mydata(ctr) = "" Then
ReDim Preserve StrData(ctr - 1)
End If

ctr = ctr + 1

Loop


StrData = Mydata
GetData = StrData
End Function

[/VBA]

I am calling this function:
[VBA]Prefix = GetData(Data)[/VBA]

But I am getting type mismatch error when the function is returning the array back to the prefix.

Why..??? Where am I going wrong..??

Sudhir

[Bob Phillips]

Explain to me in logical terms, not code terms, what that code is supposed to do because I don't get it.

[kbsudhir]

1. I am capturing the data entered into the textbox as a string into a variable.

2. I have to split the string word by word using ", " as delimiter & enter the each word into an array. Each Textboxes will have one array for it.

3. Now since I have to repeat the code 16times as to incorporate the array as per teh textbox. As per your advise I created a function.

4. I am entering the parameter as the "data" in which I am capturing the textbox data.

5. Function is spliting the string as per the delimiter of ", " & entering the resultant string after the split into the array mydata.

6. Returning the same to array "Prefix" in the end.

I hope I am making the things clear. Please let me know if I am not.

Thanks for all your precious time

Sudhir

[kbsudhir]

Basically I am giving a string as parameter to teh function as trying to return an array back to the procedure.

Thanks
Sudhir