Link to home
Start Free TrialLog in
Avatar of calebS
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.tExt
    ii = ii + 1
    userInputArray(ii).tag = "Salutation"
    userInputArray(ii).value = formAssessment.TextBox2.tExt
    ii = ii + 1
    userInputArray(ii).tag = "Amount"
    userInputArray(ii).value = formAssessment.TextBox3.tExt
    ii = ii + 1
    userInputArray(ii).tag = "Date"
    userInputArray(ii).value = formAssessment.TextBox4.tExt
    ii = ii + 1
    userInputArray(ii).tag = "AssessYear"
    userInputArray(ii).value = formAssessment.TextBox5.tExt
    ii = ii + 1
    userInputArray(ii).tag = "DueDate"
    userInputArray(ii).value = formAssessment.TextBox6.tExt
    ii = ii + 1
    userInputArray(ii).tag = "Manager"
    userInputArray(ii).value = formAssessment.ComboBox1.tExt
    ii = ii + 1
    userInputArray(ii).tag = "Partner"
    userInputArray(ii).value = formAssessment.ComboBox2.tExt
    ii = ii + 1
    userInputArray(ii).tag = "SupportStaff"
    userInputArray(ii).value = formAssessment.ComboBox3.tExt
Avatar of -Bender-
-Bender-

Hi 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-
you could store the value in the item its self.

formAssessment.ComboBox2.Tag = "Support Staff"

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
------
Avatar of calebS

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.Find
            .text = preparedDataArray(ii).tag
            .Replacement.text = preparedDataArray(ii).value
            .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.LongDate(getUserInputValue("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.getInitials(getUserInputValue("Partner"))
    tM = globalStaffLists.getInitials(getUserInputValue("Manager"))
    tOS = globalStaffLists.getInitials(getUserInputValue("SupportStaff"))
   
    If (tP <> "") And (tM <> "") Then tM = ": " & tM
    If ((tP <> "") Or (tM <> "")) And (tOS <> "") Then tOS = ": " & tOS
    tOurRef.value = tP & tM & tOS
End Function
Avatar of calebS

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,
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
 
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
Avatar of calebS

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....

Be more specific when you say "Vector"

-Brian
ASKER CERTIFIED SOLUTION
Avatar of srimanth
srimanth

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of calebS

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
Avatar of calebS

ASKER

Srimanth

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
Avatar of calebS

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