How to count an Array

**djblois1** · 05-08-2012, 12:23 PM

i am using code to count an array.

If I do it this way it works:

Dim vColumnNames As Variant
Dim iElement As Integer

vColumnNames = Array("Invoice Num", "Date", "Ord Num", "Whse Num", "Whse", "Cust Num", _
         "Customer", "Ship to Num", "Ship to", "Sls Num", "SlsPrsn", "Discount", "Item Num", "Product", _
         "Cases", "Units", "Price$", "Del", "Amt", "Total Cost", "Unit Cost", "Profit", "Weight", "Week Num", _
         "Week", "UFN", "Date Filter", "Dept", "Division", "P Cat", "Species", "Country", "S State", "S City", _
         "Group", "Sls Class", "P Man", "2 Man", "Assigned", "Region", "P Status", "Label", "Equiv")

For iElement = LBound(vColumnNames) To UBound(vColumnNames)
        fnCountArray = fnCountArray + 1
    Next iElement

However if I count the array in a separate function and pass the array to it - it does not count it correctly. Here is the code:

vColumnNames = Array("Invoice Num", "Date", "Ord Num", "Whse Num", "Whse", "Cust Num", _
         "Customer", "Ship to Num", "Ship to", "Sls Num", "SlsPrsn", "Discount", "Item Num", "Product", _
         "Cases", "Units", "Price$", "Del", "Amt", "Total Cost", "Unit Cost", "Profit", "Weight", "Week Num", _
         "Week", "UFN", "Date Filter", "Dept", "Division", "P Cat", "Species", "Country", "S State", "S City", _
         "Group", "Sls Class", "P Man", "2 Man", "Assigned", "Region", "P Status", "Label", "Equiv")

fnCountArray vColumnNames


Function fnCountArray(ParamArray vArray() As Variant)

    Dim iElement As Integer

    For iElement = LBound(vArray) To UBound(vArray)
        fnCountArray = fnCountArray + 1
    Next iElement
    
    MsgBox iElement
    
End Function

If I do it as a separate function then it only counts 1. Can someone help?

**shg** · 05-08-2012, 12:34 PM

vColumnNames is a single array.

Function fnCountArray(vArray As Variant) As Long
    If IsArray(vArray) Then
        fnCountArray = UBound(vArray) - LBound(vArray) + 1
    End If
End Function

**djblois1** · 05-08-2012, 02:07 PM

shg,

Sorry your code did work. I did not notice that you changed the function declaration. I changed that now and it worked. Thank you and everyone for the help.

**djblois1** · 05-08-2012, 01:41 PM

That still counts 1. How do I get it to count the elements within the function.

**vandan_tanna** · 05-08-2012, 01:59 PM

change your function to

Function fnCountArray(vArray As Variant)

    Dim iElement As Integer

    For iElement = LBound(vArray) To UBound(vArray)
        fnCountArray = fnCountArray + 1
    Next iElement
    
    MsgBox iElement
    
End Function

**djblois1** · 05-08-2012, 02:26 PM

Now I need to add to the end of the array. This is what I have tried:

vColumnNames = Array(vColumnNames, "Assigned")

and

vColumnNames = vColumnNames & "Assigned"

Neither one works.

**shg** · 05-08-2012, 05:13 PM

    Dim avsCol As Variant

    avsCol = Array("Invoice Num", "Date", "Ord Num")
    MsgBox avsCol(UBound(avsCol))
    
    ReDim Preserve avsCol(UBound(avsCol) + 1)
    avsCol(UBound(avsCol)) = "Whse Num"
    MsgBox avsCol(UBound(avsCol))

**djblois1** · 05-09-2012, 10:39 AM

shg,

Thank you a lot - that works. I have one more request (which I tested but am missing something). How do I get that to work with a function. Here is what I tried:

Function fnAddToArray(ByVal strToAdd As String, vArray As Variant) As Variant
    
    ReDim Preserve vArray(UBound(vArray) + 1)
    vArray(UBound(vArray)) = strToAdd

End Function

That doesn't work

**shg** · 05-09-2012, 10:47 AM

Please respect the time of people that assist, and post all the information needed to duplicate a problem. This, for example, works fine:

Sub x()
    Dim vArray As Variant
    
    vArray = Array("A", "B", "C")
    fnAddToArray "D", vArray
    MsgBox vArray(UBound(vArray))
End Sub

Function fnAddToArray(ByVal strToAdd As String, vArray As Variant) As Variant
    ReDim Preserve vArray(UBound(vArray) + 1)
    vArray(UBound(vArray)) = strToAdd
End Function

**djblois1** · 05-09-2012, 11:20 AM

shg,

I do respect the time of people that assist - my needs change as I program. Originally I was not going to put it in a function but it added a lot more code (that I would not like to do). So instead I decided to use a separate function.

Thank you for your help

**djblois1** · 05-09-2012, 11:55 AM

That is not working perfectly.

I need to do it multiple times. Here is all my code:

vColumnNames = Array("Invoice Num", "Date", "Ord Num", "Whse Num", "Whse", "Cust Num", _
         "Customer", "Ship to Num", "Ship to", "Sls Num", "SlsPrsn", "Discount", "Item Num", "Product", _
         "Cases", "Units", "Price$", "Del", "Amt", "Total Cost", "Unit Cost", "Profit", "Weight", "Week Num", _
         "Week", "UFN", "Date Filter")

fnAddToArray blnAssigned, "Assigned", vColumnNames
    fnAddToArray blnCStatus, "C Status", vColumnNames
    fnAddToArray blnCountry, "Country", vColumnNames
    fnAddToArray blnDept, "Dept", vColumnNames
    fnAddToArray blnDivison, "Divison", vColumnNames
    fnAddToArray blnEquiv, "Equiv", vColumnNames
    fnAddToArray blnGroup, "Group", vColumnNames
    fnAddToArray blnLabel, "Label", vColumnNames
    fnAddToArray blnPCat, "P Cat", vColumnNames
    fnAddToArray blnPMan, "P Man", vColumnNames
    fnAddToArray bln2Man, "2 Man", vColumnNames
    fnAddToArray blnPStatus, "P Status", vColumnNames
    fnAddToArray blnRegion, "Region", vColumnNames
    fnAddToArray blnSlsClass, "Sls Class", vColumnNames
    fnAddToArray blnSCity, "S City", vColumnNames
    fnAddToArray blnSState, "S State", vColumnNames
    fnAddToArray blnSpecies, "Species", vColumnNames
    
    Range("A1", Cells(1, fnCountArray(vColumnNames))).Value = vColumnNames

Function fnAddToArray(ByVal blnAdd As Boolean, ByVal strToAdd As String, vArray As Variant) As Variant
    
    If blnAdd Then
        ReDim Preserve vArray(UBound(vArray) + 1)
        vArray(UBound(vArray)) = strToAdd
    End If

End Function

If I leave the + 1 in there then there will be a blank column between the original array and the first added column. If I remove the + 1, then there is no blank column but each time something adds to the array it writes over the last thing that was written. So if I added "Assigned" and "Region" - "Assigned" would be added then it would be written over by "Region"

**shg** · 05-09-2012, 12:03 PM

Once again, this works fine:

Sub x()
    Dim vColumnNames As Variant

    vColumnNames = Array("Invoice Num", "Date", "Ord Num", "Whse Num", "Whse", "Cust Num", _
                         "Customer", "Ship to Num", "Ship to", "Sls Num", "SlsPrsn", "Discount", "Item Num", "Product", _
                         "Cases", "Units", "Price$", "Del", "Amt", "Total Cost", "Unit Cost", "Profit", "Weight", "Week Num", _
                         "Week", "UFN", "Date Filter")

    fnAddToArray True, "Assigned", vColumnNames
    fnAddToArray True, "C Status", vColumnNames
    fnAddToArray True, "Country", vColumnNames
    fnAddToArray True, "Dept", vColumnNames
    fnAddToArray True, "Divison", vColumnNames
    fnAddToArray True, "Equiv", vColumnNames
    fnAddToArray True, "Group", vColumnNames
    fnAddToArray True, "Label", vColumnNames
    fnAddToArray True, "P Cat", vColumnNames
    fnAddToArray True, "P Man", vColumnNames
    fnAddToArray True, "2 Man", vColumnNames
    fnAddToArray True, "P Status", vColumnNames
    fnAddToArray True, "Region", vColumnNames
    fnAddToArray True, "Sls Class", vColumnNames
    fnAddToArray True, "S City", vColumnNames
    fnAddToArray True, "S State", vColumnNames
    fnAddToArray True, "Species", vColumnNames

    Range("A1").Resize(, UBound(vColumnNames) - LBound(vColumnNames) + 1).Value = vColumnNames
End Sub

Function fnAddToArray(ByVal blnAdd As Boolean, ByVal strToAdd As String, vArray As Variant) As Variant
    If blnAdd Then
        ReDim Preserve vArray(UBound(vArray) + 1)
        vArray(UBound(vArray)) = strToAdd
    End If
End Function

**djblois1** · 05-09-2012, 12:11 PM

snb,

That is doing the same exact thing that my code did. It is adding a blank column between the original array and the first column I add.

**shg** · 05-09-2012, 12:29 PM

I'm not snb.

Did you try running the code as posted?

**djblois1** · 05-09-2012, 12:40 PM

Yes, the only line that you changed was this:

Range("A1").Resize(, UBound(vColumnNames) - LBound(vColumnNames) + 1).Value = vColumnNames

It does exactly the same as my code - it adds a blank column between the Original Array and the first item that I add.

Sorry for messing up your name.

**shg** · 05-09-2012, 12:43 PM

The code I posted doesn't result in any blank cells. I have no further suggestions, sorry.

**JosephP** · 05-09-2012, 01:07 PM

is all that code in the same module and do you have an option base 1 statement anywhere?

**shg** · 05-09-2012, 01:13 PM

It works with option base 1.

**JosephP** · 05-09-2012, 01:22 PM

even if the function code is in a different module without an option base 1? I haven't tried (on a cell)-I'm just trying to think of anything that might cause the claimed behavior.

**shg** · 05-09-2012, 01:37 PM

Dunno, Joseph. There are disconnected fragments of code with no context, and I'm not up for 20 Questions today.

Please feel free to sort it out.

**JosephP** · 05-09-2012, 01:46 PM

logically it would produce that effect. the redim preserve would change the lower bound to 0 on first pass while increasing the upper bound, everything shifts up and the last two slots are now empty and only the last one gets populated. hence blank column before first added item.

**shg** · 05-09-2012, 01:54 PM

Yup, that could happen, or conversely, you could overwrite the last element. That was my point about posting code out of context.

**shg** · 05-09-2012, 02:11 PM

Test1 leaves a gap, test2 overwrites an element, test3 works irrespective of option base.

Option Explicit

Option Base 1

Sub test1()
    Dim av          As Variant

    av = Array(1, 2, 3)

    Extend1 4, av
    Rows(1).ClearContents
    Range("A1").Resize(, UBound(av) - LBound(av) + 1).Value = av
End Sub

Function Extend1(v As Variant, av As Variant) As Variant
    ReDim Preserve av(0 To UBound(av) + 1)
    av(UBound(av)) = v
End Function

Sub test2()
    Dim av          As Variant

    av = VBA.Array(1, 2, 3)

    Extend2 4, av
    Rows(1).ClearContents
    Range("A1").Resize(, UBound(av) - LBound(av) + 1).Value = av
End Sub

Function Extend2(v As Variant, av As Variant) As Variant
    ReDim Preserve av(1 To UBound(av) + 1)
    av(UBound(av)) = v
End Function

Sub test3()
    Dim av          As Variant

    av = Array(1, 2, 3)

    Extend3 4, av
    Rows(1).ClearContents
    Range("A1").Resize(, UBound(av) - LBound(av) + 1).Value = av
End Sub

Function Extend3(v As Variant, av As Variant) As Variant
    ReDim Preserve av(UBound(av) + 1)
    av(UBound(av)) = v
End Function

**JosephP** · 05-09-2012, 04:45 PM

but if Extend3 were in a different module, with no option base statement, test3 would have the same problem as test1.

**shg** · 05-09-2012, 05:17 PM

I didn't test, but the solution is to not use Option Base -- it's never necessary.

**JosephP** · 05-09-2012, 05:26 PM

or at the very least use it consistently.
of course using VBA.Array instead of just Array would also cure it since it ignores the option base

How to count an Array

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Re: How to count an Array

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions