calebS
asked on
VBA Array: or is there a better way?
Posted below is a snippet of code, I am wondering if there is a better way.
Namely, is there a type of vector for arrays.
I am guessing one would exist in some form.
Regards,
Cassandra
**********Code Below********************* ****
'userInputArray is declared earlier.
'An iterator is used below to make making changes easier.
Dim ii As Integer
ii = 0
userInputArray(ii).tag = "ClientCode"
userInputArray(ii).value = formAssessment.TextBox1.tE xt
ii = ii + 1
userInputArray(ii).tag = "Salutation"
userInputArray(ii).value = formAssessment.TextBox2.tE xt
ii = ii + 1
userInputArray(ii).tag = "Amount"
userInputArray(ii).value = formAssessment.TextBox3.tE xt
ii = ii + 1
userInputArray(ii).tag = "Date"
userInputArray(ii).value = formAssessment.TextBox4.tE xt
ii = ii + 1
userInputArray(ii).tag = "AssessYear"
userInputArray(ii).value = formAssessment.TextBox5.tE xt
ii = ii + 1
userInputArray(ii).tag = "DueDate"
userInputArray(ii).value = formAssessment.TextBox6.tE xt
ii = ii + 1
userInputArray(ii).tag = "Manager"
userInputArray(ii).value = formAssessment.ComboBox1.t Ext
ii = ii + 1
userInputArray(ii).tag = "Partner"
userInputArray(ii).value = formAssessment.ComboBox2.t Ext
ii = ii + 1
userInputArray(ii).tag = "SupportStaff"
userInputArray(ii).value = formAssessment.ComboBox3.t Ext
Namely, is there a type of vector for arrays.
I am guessing one would exist in some form.
Regards,
Cassandra
**********Code Below*********************
'userInputArray is declared earlier.
'An iterator is used below to make making changes easier.
Dim ii As Integer
ii = 0
userInputArray(ii).tag = "ClientCode"
userInputArray(ii).value = formAssessment.TextBox1.tE
ii = ii + 1
userInputArray(ii).tag = "Salutation"
userInputArray(ii).value = formAssessment.TextBox2.tE
ii = ii + 1
userInputArray(ii).tag = "Amount"
userInputArray(ii).value = formAssessment.TextBox3.tE
ii = ii + 1
userInputArray(ii).tag = "Date"
userInputArray(ii).value = formAssessment.TextBox4.tE
ii = ii + 1
userInputArray(ii).tag = "AssessYear"
userInputArray(ii).value = formAssessment.TextBox5.tE
ii = ii + 1
userInputArray(ii).tag = "DueDate"
userInputArray(ii).value = formAssessment.TextBox6.tE
ii = ii + 1
userInputArray(ii).tag = "Manager"
userInputArray(ii).value = formAssessment.ComboBox1.t
ii = ii + 1
userInputArray(ii).tag = "Partner"
userInputArray(ii).value = formAssessment.ComboBox2.t
ii = ii + 1
userInputArray(ii).tag = "SupportStaff"
userInputArray(ii).value = formAssessment.ComboBox3.t
you could store the value in the item its self.
formAssessment.ComboBox2.T ag = "Support Staff"
Dont know if thats any help
-Brian
formAssessment.ComboBox2.T
Dont know if thats any help
-Brian
You can then loop through all controls on your form:
---
Dim o As Control
For Each o In Me
If TypeOf o Is TextBox Or TypeOf o Is ComboBox Then
MsgBox o.Tag
MsgBox o.Text
End If
Next
------
---
Dim o As Control
For Each o In Me
If TypeOf o Is TextBox Or TypeOf o Is ComboBox Then
MsgBox o.Tag
MsgBox o.Text
End If
Next
------
ASKER
Brian, Bender
Thanks.
--the background
I am working with some old code (a macro that I wrote for work that took about 20hrs). The macro is used to create letters. ie previously we opened a template and manually filled in the information. This macro automates that. It was a bit of a hack job when I wrote it, and now (12 months later) I have convinced the bosses to let me create more macros. My first step has been to go back over this macro. It is only about 10 pages long, and can be a lot shorter. The aim is that I can use it as somewhat of a template to create more macros. In that way each macro should only take a few hours.
--what the array does
The macro works in two parts.
First the user is presented with a form. This form has various ways to input data. This data is then pulled from all these input types and entered into the above array.
The reason behind this is that the next module is isolated from the form. I can change the form in anyway I like and the next module is not effected as long as I maintain the convention used in the UserInputArray. When the user presses 'okay' the UserInputArray is filled (as per the above code) and the next module is called.
In the next module uses the UserInputArray, which is raw input, and transforms it into a similar type of array. (I have put a snippet of this code below).
The reason I am not simply using the textboxes is because the two modules are linked by one very simple to understand array. I can make changes to the form, turn a textbox into a combobox, add tabs etc, and only the code is only effected in a minimal way.
So if this makes sense, I really do want an array, or C++ style vector(preferably) of some sort.
Thanks,
Cassandra
*****Code Below******
Public Sub CreateLetter()
preparedDataArray(0) = tDate()
preparedDataArray(1) = tOurRef()
Call fillFields
End Sub
Private Sub fillFields()
For ii = 0 To (NUMTAGS - 1)
With ActiveDocument.Content.Fin d
.text = preparedDataArray(ii).tag
.Replacement.text = preparedDataArray(ii).valu e
.Execute replace:=wdReplaceAll, Forward:=True, Wrap:=wdFindContinue
End With
Next ii
End Sub
Private Function tDate() As Data
tDate.tag = "###_tDate_###"
If (IsDate(getUserInputValue( "Date"))) Then
tDate.value = moduleDateFunctions.LongDa te(getUser InputValue ("Date"))
Else
tDate.value = "Insert Date Here"
End If
End Function
Private Function tOurRef() As Data
tOurRef.tag = "###_tOurRef_###"
Dim tP, tM, tOS As String
tP = globalStaffLists.getInitia ls(getUser InputValue ("Partner" ))
tM = globalStaffLists.getInitia ls(getUser InputValue ("Manager" ))
tOS = globalStaffLists.getInitia ls(getUser InputValue ("SupportS taff"))
If (tP <> "") And (tM <> "") Then tM = ": " & tM
If ((tP <> "") Or (tM <> "")) And (tOS <> "") Then tOS = ": " & tOS
tOurRef.value = tP & tM & tOS
End Function
Thanks.
--the background
I am working with some old code (a macro that I wrote for work that took about 20hrs). The macro is used to create letters. ie previously we opened a template and manually filled in the information. This macro automates that. It was a bit of a hack job when I wrote it, and now (12 months later) I have convinced the bosses to let me create more macros. My first step has been to go back over this macro. It is only about 10 pages long, and can be a lot shorter. The aim is that I can use it as somewhat of a template to create more macros. In that way each macro should only take a few hours.
--what the array does
The macro works in two parts.
First the user is presented with a form. This form has various ways to input data. This data is then pulled from all these input types and entered into the above array.
The reason behind this is that the next module is isolated from the form. I can change the form in anyway I like and the next module is not effected as long as I maintain the convention used in the UserInputArray. When the user presses 'okay' the UserInputArray is filled (as per the above code) and the next module is called.
In the next module uses the UserInputArray, which is raw input, and transforms it into a similar type of array. (I have put a snippet of this code below).
The reason I am not simply using the textboxes is because the two modules are linked by one very simple to understand array. I can make changes to the form, turn a textbox into a combobox, add tabs etc, and only the code is only effected in a minimal way.
So if this makes sense, I really do want an array, or C++ style vector(preferably) of some sort.
Thanks,
Cassandra
*****Code Below******
Public Sub CreateLetter()
preparedDataArray(0) = tDate()
preparedDataArray(1) = tOurRef()
Call fillFields
End Sub
Private Sub fillFields()
For ii = 0 To (NUMTAGS - 1)
With ActiveDocument.Content.Fin
.text = preparedDataArray(ii).tag
.Replacement.text = preparedDataArray(ii).valu
.Execute replace:=wdReplaceAll, Forward:=True, Wrap:=wdFindContinue
End With
Next ii
End Sub
Private Function tDate() As Data
tDate.tag = "###_tDate_###"
If (IsDate(getUserInputValue(
tDate.value = moduleDateFunctions.LongDa
Else
tDate.value = "Insert Date Here"
End If
End Function
Private Function tOurRef() As Data
tOurRef.tag = "###_tOurRef_###"
Dim tP, tM, tOS As String
tP = globalStaffLists.getInitia
tM = globalStaffLists.getInitia
tOS = globalStaffLists.getInitia
If (tP <> "") And (tM <> "") Then tM = ": " & tM
If ((tP <> "") Or (tM <> "")) And (tOS <> "") Then tOS = ": " & tOS
tOurRef.value = tP & tM & tOS
End Function
ASKER
As you can see the above code never needs to concern itself with how the form is designed.
To find out information about the UserInputArray with the tag "Manager" the program calls a little sub (getUserInputValue) that searches the array for the tag and returns the value.
Thanks,
To find out information about the UserInputArray with the tag "Manager" the program calls a little sub (getUserInputValue) that searches the array for the tag and returns the value.
Thanks,
I cant really spend too much time on this question, but ill give you a few tips
1) Split up the array's into categories ie (manager, supportstaff, partner arrays) if thats how you are going to be doing a lot of sorting. Or split it up by the peoples last names ie (a array, b array)...etc
2) Use type libraries for organizing the data
Type Employees
FirstName as string * 10 'allow 10 bytes for the first name
LastName as string * 10
UsersPostion as integer '1 = manager,2 = supportstaff, 3 = partner...
'....whatever else you need
end type
1) Split up the array's into categories ie (manager, supportstaff, partner arrays) if thats how you are going to be doing a lot of sorting. Or split it up by the peoples last names ie (a array, b array)...etc
2) Use type libraries for organizing the data
Type Employees
FirstName as string * 10 'allow 10 bytes for the first name
LastName as string * 10
UsersPostion as integer '1 = manager,2 = supportstaff, 3 = partner...
'....whatever else you need
end type
ohh yah
3) Dont store the data in an array at all, store in an Access Data base and use ADO to sort it for you.
-Brian
3) Dont store the data in an array at all, store in an Access Data base and use ADO to sort it for you.
-Brian
ASKER
Thanks,
I was already considering making use a a database. I think you missed my point with the your other suggestions.
I have no need for categories or any complex sorting methods as the arrays are not very big.
I still would prefer a vector of some sort....
I was already considering making use a a database. I think you missed my point with the your other suggestions.
I have no need for categories or any complex sorting methods as the arrays are not very big.
I still would prefer a vector of some sort....
Be more specific when you say "Vector"
-Brian
-Brian
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Personally I like the Dictionary as the best option, but if you want more functionality, like sorting or filtering, you could build your own ADO recordset and use it. There is no need to connect the recordset to a database.
However, there is a lot of overhead involved in using the recordset instead of the Dictionary, and a dictionary can take any type of object with a key associated to it, which in some aspects makes it even better than an ADO recordset.
However, there is a lot of overhead involved in using the recordset instead of the Dictionary, and a dictionary can take any type of object with a key associated to it, which in some aspects makes it even better than an ADO recordset.
ASKER
Brian,
>be more specific when you say vector
I am talking about a C++ style vector
ie a dynamic array with .push() and .pop() type functions
>be more specific when you say vector
I am talking about a C++ style vector
ie a dynamic array with .push() and .pop() type functions
ASKER
Srimanth
I will look into the dictionary object thanks.
I will look into the dictionary object thanks.
Okay, first off .push() and .pop() are not available in Visual Basic. Secondly, a vector in C++ logically would be the same as a String Array.
-Brian
-Brian
ASKER
I disagree, a vector in C++ is not the same as a String Array.
Especially considering I am working with a user-defined data type.
Even more than that, a vector is an object, with member functions etc. I was just after the same sort of functionality. In fact, isn't there a 'bag' container or some sort? distant bells are ringing, I shall go do some more research.
Thanks,
Cassandra
Especially considering I am working with a user-defined data type.
Even more than that, a vector is an object, with member functions etc. I was just after the same sort of functionality. In fact, isn't there a 'bag' container or some sort? distant bells are ringing, I shall go do some more research.
Thanks,
Cassandra
Why do you want to store values this way? All controls (textboxes, comboboxes,...) contain all values you need... Can you tell me what you're planning to do when array is filled?
-Bender-