YZlat
asked on
vba arrays
how do I declare a global array and add values to it programmatically from one of the subforms?
In a standard module ...
Public pvarArray()
in the form code
this will retain the data that already exists in the array and add 1 more element for you to store new data in.
ReDim Preserve pvarArray(UBound(pvarArray ) + 1)
then to add a new value
pvarArray(UBound(pvarArray )) = "YourValueHere"
Steve
Public pvarArray()
in the form code
this will retain the data that already exists in the array and add 1 more element for you to store new data in.
ReDim Preserve pvarArray(UBound(pvarArray
then to add a new value
pvarArray(UBound(pvarArray
Steve
ASKER
Steve, I get an error in this line
ReDim Preserve pvarArray(UBound(pvarArray ) + 1)
run-time error '9' Subscript out of range
ReDim Preserve pvarArray(UBound(pvarArray
run-time error '9' Subscript out of range
genric all purpose function ....
Public pvarArray As Variant
Public Function AddToArray(NewValue As Variant)
If IsArray(pvarArray) Then
ReDim Preserve pvarArray(UBound(pvarArray ) + 1)
pvarArray(UBound(pvarArray )) = NewValue
Else
pvarArray = Array(NewValue)
End If
End Function
Steve
Public pvarArray As Variant
Public Function AddToArray(NewValue As Variant)
If IsArray(pvarArray) Then
ReDim Preserve pvarArray(UBound(pvarArray
pvarArray(UBound(pvarArray
Else
pvarArray = Array(NewValue)
End If
End Function
Steve
ASKER
that did not work either
ASKER
I tried a different approach:
I declared Public myArray as Variant in one of the public modules.
Then in my code for the subform I put
ReDim Preserve myArray(UBound(myArray) + 1)
myArray = Array(myValue)
but then i started to get a type mismatch error in line ReDim Preserve myArray(UBound(myArray) + 1)
I declared Public myArray as Variant in one of the public modules.
Then in my code for the subform I put
ReDim Preserve myArray(UBound(myArray) + 1)
myArray = Array(myValue)
but then i started to get a type mismatch error in line ReDim Preserve myArray(UBound(myArray) + 1)
YZlat:
Did you try my example? If you want a dynamic array, you can change it this way.
Public myArray() As String
To add values to it, you would need to do something like this.
Dim n As Long
For n = 1 to 10
'This will fill the array with the value of a textbox on the subform
Redim Preserve myArray(n)
myArray(n) = Forms!mySubform!myTextBox
Next
Joe
Did you try my example? If you want a dynamic array, you can change it this way.
Public myArray() As String
To add values to it, you would need to do something like this.
Dim n As Long
For n = 1 to 10
'This will fill the array with the value of a textbox on the subform
Redim Preserve myArray(n)
myArray(n) = Forms!mySubform!myTextBox
Next
Joe
that is because a variant is not an array until you make it one as I coded for specifically in my function. Wwe can expand it to handle any publically declared variant variable ...
Public Function AddToArray(PublicArray As Variant, NewValue As Variant)
If IsArray(PublicArray) Then
ReDim Preserve PublicArray(UBound(PublicA rray) + 1)
PublicArray(UBound(PublicA rray)) = NewValue
Else
PublicArray = Array(NewValue)
End If
End Function
then in your form you pass the array to add the value to and the value to be added to the AddToArray function.
AddToArray(myArray, myValue)
Steve
Public Function AddToArray(PublicArray As Variant, NewValue As Variant)
If IsArray(PublicArray) Then
ReDim Preserve PublicArray(UBound(PublicA
PublicArray(UBound(PublicA
Else
PublicArray = Array(NewValue)
End If
End Function
then in your form you pass the array to add the value to and the value to be added to the AddToArray function.
AddToArray(myArray, myValue)
Steve
ASKER
joekendall, I guess I did not make it very clear what I need. I have a form and a subform. A subform ia a datasheet form and when the user clicks on one cell, the contents of the cell should be added to an array. The user should be able to add multiple items.
Then when the button clicked, the contents of an array will be manipulated further.
And if the form is closed or reloaded I want the array to empty itself.
Then when the button clicked, the contents of an array will be manipulated further.
And if the form is closed or reloaded I want the array to empty itself.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Public myArray(10) As String
To add values to it, you would need to do something like this.
Dim n As Long
For n = 1 to 10
'This will fill the array with the value of a textbox on the subform
myArray(n) = Forms!mySubform!myTextBox
Next
Joe