Excel VBA

Using Enum

We had a case where data was scattered all over a massive spreadsheet rather than organized in rows and columns. Dave and I changed it so that we still went 40 places to get the data, but it was now brought into a structure or array.

The program then turned around and read from the array to perform the eventual ‘screen scrape’ to send to the mainframe screens.


Public Enum mwRateRowCols
    LongDesc = 1
    VCat = 4
    ExtHour = 6
    ExtDay = 7
    DailyRate = 8              'Same value as the one day rate (DayRate1)
    MileHour = 12
    MileDay = 13
    MileWeek = 14
    MileChg = 15
End Enum

Type mwAllRates
    StartDate           As Date
    EndDate             As Date
    VCategory           As String
    DayRate1            As String
    DailyRate           As String
    ExtHour             As String
    ExtDay              As String
    MarkupPerc          As String
    ExcelRateRow        As Long
    MileHour            As String
    MileDay             As String
    MileWeek            As String
    MileChg             As String
    ExcelStartDateRow   As Long
    ExcelStartDateCol   As Long
End Type

Type mwRateUpdateUDT
    Brand               As String
    LocalBoth     As String
    Station             As Boolean
    Mileage             As Integer
    ContractID          As String
    LocName             As String
    ProductCode         As String
    RateMarkup              As String
    Country                 As String
    CurrencyType            As String
    mwRates()               As mwAllRates
End Type

Type mwRateDates
    StartDate           As Date
    EndDate             As Date
    ExcelStartDateRow   As Long
    ExcelStartDateCol   As Long
End Type

Public mwExcelRateSheets()                  As mwRateUpdateUDT
Public mwRateBlockRateDates()               As mwRateDates
Public mwRateRows(mwRateRowsPerRateBlock)   As mwAllRates

'+---------------------------------------------------------------------------+
'|   Using the ENUM                                                          |
'+---------------------------------------------------------------------------+

      For intIndex = 0 To (mwRateRowsPerRateBlock)
        lngWSRow = 12 + RateBlockOffset + intIndex
        
        If Trim(Cells(lngWSRow, 4).value) <> "" Then
            blnRetrieveRateRows = True
            With mwRateRows(intOffset)
                .VCategory = Cells(lngWSRow, mwRateRowCols.VCat).value   'Name replaces remembering the column #
                .DayRate1 = Cells(lngWSRow, mwRateRowCols.DailyRate).value
                .ExtHour = Cells(lngWSRow, mwRateRowCols.ExtHour).value
                .ExtDay = Cells(lngWSRow, mwRateRowCols.ExtDay).value
                .MileChg = Cells(lngWSRow, mwRateRowCols.MileChg).value
                .MileDay = Cells(lngWSRow, mwRateRowCols.MileDay).value
                .MileWeek = Cells(lngWSRow, mwRateRowCols.MileWeek).value  ' If the things column changes, update # in Enum
                .MileHour = Cells(lngWSRow, mwRateRowCols.MileHour).value
                
                If Trim(.DayRate1) = "" Or Trim(.ExtHour) = "" Or Trim(.ExtDay) = "" Then
                    ActiveSheet.Unprotect SecretPassword 'We set a Const for the password & didn't tell worker bees
                    Range(Cells(lngWSRow, mwRateRowCols.DailyRate), Cells(lngWSRow, mwRateRowCols.ExtDay)).Interior.Color = RGB(255, 0, 0)
                    ActiveSheet.Protect SecretPassword

By using Enum, if a column moved or the client changed their mind, we changed only the number in the Enum and not 30 places in the VBA code.

Thanks Dav for teaching this lesson.

Updated:	06/21/2024 07:42
This page added:	27 March 2013