First, using input and a “Search” command button, my code declares many viariables that sort data (Zip, Client Name, Last Service Date, etc.) from an excel spreadsheet and place it into a VBA userForm ListBox. Multiple clients will are added line by line. Once in the listbox, a command button “Continue” will use the data to place that data plus a few other pieces of new data(Specific messages, Time Since Service, Phone Number) into a new exel worksheet.

Instead of having similar code with many variables for the “Continue” feature(the second search), I would rather have an object, sub or some other device to hold/create variables and simplify the code.

The following functions are part of the Sub and dynamically retrive the index of cells using listColumn references. The dataColOffset() function is used to find the offset of cells being referenced.

Public Function dataColIndex(colName As String) As Integer

    Dim lo As ListObject
    Set lo = Worksheets("Database").ListObjects("Table3")
    
    dataColIndex = lo.ListColumns(colName).Index
    
    End Function
    
    
    Public Function dataColOffset(changeItem As String, searchItem As String) As Integer
    
    dataColOffset = dataColIndex(changeItem) - dataColIndex(searchItem)

The following Sub has variables that retrieve data from an excel worksheet when a search bigins. This is the first search which will later have code to add names to the listBox. For now, I am stuck on trying to simplify.

Private Sub CommandButton3_Click()
    Dim SearchCol As Range
    SearchBy = ComboBox1.Text
    SearchValue = TextBox1.Text
    Amount = ComboBox2.Text
    Set SearchCol = Worksheets("Database").ListObjects("Table3").ListColumns(SearchBy)

    Dim Name As String
    Dim Zip As Integer
    Dim ContactDate As Date
    Dim ContactAttempt As Date
    Dim ContactMethod As String
    Dim Company As Sring
    Dim LastTuned As Date
    Dim MonthsPerTune As Integer
    Dim City As String
    
    For Each Cell In SearchCol
        'set the columns searched
        Set Name = Cell.Offset(0, dataColOffset("Full Name", SearchBy))
        Set Zip = Cell.Offset(0, dataColOffset("ZipCode", SearchBy))
        Set ContactDate = Cell.Offset(0, dataColOffset("Successful Contact Date", SearchBy))
        Set ContactAttempt = Cell.Offset(0, dataColOffset("Contact Attempt Date", SearchBy))
        Set ContactMethod = Cell.Offset(0, dataColOffset("Contact Method", SearchBy))
        Set Company = Cell.Offset(0, dataColOffset("Company", SearchBy))
        Set LastTuned = Cell.Offset(0, dataColOffset("Last Tuned", SearchBy))
        Set MonthsPerTune = Cell.Offset(0, dataColOffset("Month Per Tune", SearchBy))
        Set City = Cell.Offset(0, dataColOffset("City", SearchBy))
        Next
End Sub

As I stated previosuly, I would rather store these variables in something else such as an object or another sub so that I can place the code in the first and second search Subroutines instead of writing it all over agian. Any ideas to store these variables more easily?

A few options I have thought about are:

Is there some way to use getters and setters to create objects without having to set the member variables manually in each sub?

Is there a way to call a sub to create ClientObject(s) that won’t go out of scope within another sub?

Any other ideas?