The Daily Dose

I’ve got this bit of code where I’m listing table fields that I’m going to eventually Join into a SELECT statement.

fields(1) = "xuid"
    fields(1) = "base_product"
    fields(2) = "BillOfLading"
    fields(3) = "BillOfLadingDate"
    fields(4) = "BuyerCustomId"
    fields(5) = "BuyerLegalName"
    fields(6) = "CarrierCustomId"
    fields(7) = "DestinationCustomId DestCustID"
    fields(8) = "DestinationJurisdiction DestJur"
    fields(9) = "DivDestJurisdiction DivDest"
    fields(10) = "GrossUnits Gross"
    fields(11) = "NetUnits Net"
    fields(12) = "OriginCustomId"
    fields(13) = "OriginJurisdiction OriJur"
    fields(14) = "OriginTerminalCode TermCode"
    fields(15) = "SellerCustomId"
    fields(16) = "SellerLegalName"
    fields(17) = "BOLState"

As you can see, I needed to add a new field in position 1. Now I’m faced with renumbering the rest of the array. Terrible. So I wrote this:

Public Sub RenumberArray()
    
    Dim doClip As MSForms.DataObject
    Dim vaLines As Variant, vaLine As Variant, vaLineStart As Variant
    Dim i As Long
    
    Set doClip = New MSForms.DataObject
    
    doClip.GetFromClipboard
    vaLines = Split(doClip.GetText, vbNewLine)
    
    For i = LBound(vaLines) To UBound(vaLines)
        vaLine = Split(vaLines(i), ")", 2)
        vaLineStart = Split(vaLine(0), "(")
        vaLines(i) = vaLineStart(0) & "(" & i + 1 & ")" & vaLine(1)
    Next i
    
    doClip.SetText Join(vaLines, vbNewLine)
    doClip.PutInClipboard
    
End Sub

Now I can copy the code, run this procedure, and paste the results.

fields(1) = "xuid"
    fields(2) = "base_product"
    fields(3) = "BillOfLading"
    fields(4) = "BillOfLadingDate"
    fields(5) = "BuyerCustomId"
    fields(6) = "BuyerLegalName"
    fields(7) = "CarrierCustomId"
    fields(8) = "DestinationCustomId DestCustID"
    fields(9) = "DestinationJurisdiction DestJur"
    fields(10) = "DivDestJurisdiction DivDest"
    fields(11) = "GrossUnits Gross"
    fields(12) = "NetUnits Net"
    fields(13) = "OriginCustomId"
    fields(14) = "OriginJurisdiction OriJur"
    fields(15) = "OriginTerminalCode TermCode"
    fields(16) = "SellerCustomId"
    fields(17) = "SellerLegalName"
    fields(18) = "BOLState"

Ahhh. Satisfying. Here’s how the stuff inside the loop works.

vaLine = Split(vaLines(i), ")", 2)

This splits the line into:

vaLine
0	fields(17
1	= “BOLState”

vaLineStart = Split(vaLine(0), "(")

This results in:

vaLineStart
0	fields
1	17

Then I just concatenate the relevant parts back together with a different number.

fields & ( & 18 & ) & = "BOLState"