Renumbering Arrays in Code

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"

7 thoughts on “Renumbering Arrays in Code”

In Excel:

Sub M_snb()
  ActiveSheet.Paste Cells(1)
  MsgBox Join([transpose(substitute("fields("&row(1:18) & ")" & mid(A1:A18,15,100),"))",")"))], vbLf)
  ActiveSheet.Cells(1).CurrentRegion.ClearContents
End Sub

Sub M_snb()

ActiveSheet.Paste Cells(1)

MsgBox Join([transpose(substitute("fields("&row(1:18) & ")" & mid(A1:A18,15,100),"))",")"))], vbLf)

ActiveSheet.Cells(1).CurrentRegion.ClearContents

Kelyn Mann says:

October 16, 2017 at 8:59 am

Great solution. If only you would have posted this a month ago, it would have saved me a lot of headache!
James Brown says:

October 17, 2017 at 8:52 am

I agree that is terrible. Why would you hard code numbers while building the array?

Dim d As New Scripting.dictionary
d.Add “xuid”, vbNullString
d.Add “base_product”, vbNullString
d.Add “BillOfLading”, vbNullString
d.Add “BillOfLadingDate”, vbNullString
d.Add “BuyerCustomId”, vbNullString
d.Add “BuyerLegalName”, vbNullString
d.Add “CarrierCustomId”, vbNullString
d.Add “DestinationCustomId DestCustID”, vbNullString
d.Add “DestinationJurisdiction DestJur”, vbNullString
d.Add “DivDestJurisdiction DivDest”, vbNullString
d.Add “GrossUnits Gross”, vbNullString
d.Add “NetUnits Net”, vbNullString
d.Add “OriginCustomId”, vbNullString
d.Add “OriginJurisdiction OriJur”, vbNullString
d.Add “OriginTerminalCode TermCode”, vbNullString
d.Add “SellerCustomId”, vbNullString
d.Add “SellerLegalName”, vbNullString
d.Add “BOLState”, vbNullString

Debug.Print Join(d.Keys, “,”)
Dick Kusleika says:

October 17, 2017 at 5:40 pm

Well James, thanks a lot. Now I have to rewrite all my code to use dictionaries. Right after I’m done kicking myself.
Jeff Weir says:

October 17, 2017 at 8:42 pm

Yeah, dics are awesome. I always late-bind mine so I don’t have to bother setting references, and so that my code works on other folks’ machines without me having to go set references there too.

Dim dic As Object
Set dic = CreateObject(“scripting.dictionary”)
snb says:

October 18, 2017 at 3:55 am

You don’t need as ditionary to create that array.
Use split(“xuid base_product”) instead.
And avoid spaces in fieldnames.
Gyansetu says:

January 16, 2018 at 5:55 am

It is good technique

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

7 thoughts on “Renumbering Arrays in Code”

Leave a Reply