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.

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:

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

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

This splits the line into:

vaLine
0 fields(17
1 = “BOLState”

This results in:

vaLineStart
0 fields
1 17

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

7 thoughts on “Renumbering Arrays in Code

  1. In Excel:

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

  3. 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, “,”)

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

  5. 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”)

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


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

Leave a Reply

Your email address will not be published.