array – Daily Dose of Excel

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"

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

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"

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)

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

This splits the line into:

vaLine
0	fields(17
1	= “BOLState”

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

1	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"

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

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

Tag: array

Renumbering Arrays in Code