How can I pass an Array so I can get the verable count?

USAMax

Well-known Member
Joined
May 31, 2006
Messages
832
Office Version
  1. 365
Platform
  1. Windows
I have a routine that counts variables but I have to pass a string so it knows which one to test. There has to be a better way.

Code:
Private Function ArrayVariableCount(Arr As String)
    Dim nonEmptyElements As Integer, i As Integer           '
    nonEmptyElements = 0: i = 0                             '
    If Arr = "SOA" Then                                     '
        For i = LBound(SOA) To UBound(SOA)                  '
            If Not SOA(i) = "" Then                      '
                nonEmptyElements = nonEmptyElements + 1     '
            End If                                          '
        Next                                                '
    Else                                                    '
        If Arr = "COA" Then                                 '
            For i = LBound(COA) To UBound(COA)              '
                If Not COA(i, 0) = "" Then                  '
                    nonEmptyElements = nonEmptyElements + 1 '
                End If                                      '
            Next                                            '
        End If                                              '
    End If
    ArrayVariableCount = nonEmptyElements                   '
End Function
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
What are SOA and COA -- public arrays? Of what type?

Why not just pass the array of interest to the function?

Code:
[PLAIN]Private Function CountEm(asInp() As String)
    Dim i As Long
    

    For i = LBound(asInp) To UBound(asInp)
        If Len(asInp(i)) Then CountEm = CountEm + 1
    Next i
End Function
[/PLAIN]
 

USAMax

Well-known Member
Joined
May 31, 2006
Messages
832
Office Version
  1. 365
Platform
  1. Windows
Thank you for the fast response! I see you even improved my counter, thank you!

They are both public arrays an as you can see they have a different number of parameters.

I will not be able to try this for a couple of hours but how will I pass the array. CountEm(COA), CountEm(COA()), CountEm(COA())?
 

USAMax

Well-known Member
Joined
May 31, 2006
Messages
832
Office Version
  1. 365
Platform
  1. Windows
Thank you for the fast response! I see you even improved my counter, thank you!

They are both public arrays an as you can see they have a different number of parameters.

I will not be able to try this for a couple of hours but how will I pass the array. CountEm(COA), CountEm(COA()), CountEm(COA())?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Code:
myCount =  CountEm(COA)
 

USAMax

Well-known Member
Joined
May 31, 2006
Messages
832
Office Version
  1. 365
Platform
  1. Windows
Thank you so much, that is exactly what I was looking for!

You're amazing!
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

You're welcome.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
What are SOA and COA -- public arrays? Of what type?

Why not just pass the array of interest to the function?

Code:
[PLAIN]Private Function CountEm(asInp() As String)
    Dim i As Long
    

    For i = LBound(asInp) To UBound(asInp)
        If Len(asInp(i)) Then CountEm = CountEm + 1
    Next i
End Function
[/PLAIN]
@Everyone,

Whatever you do, make sure to use shg's code and not what follows (as it is almost assurely slower than shg's code), but I was curious to see what this function would look like as a one-liner...

Code:
Private Function CountEm(asInp() As String) As Long
  CountEm = UBound(Split(Application.Trim(Replace(Replace(Join(asInp, Chr(1)), " ", Chr(2)), Chr(1), " ")))) + 1
End Function



@shg

I noticed in the tags you place around your code, you use "[noparse]" and its slashed companion... what do those tags do?
 

USAMax

Well-known Member
Joined
May 31, 2006
Messages
832
Office Version
  1. 365
Platform
  1. Windows
Oh crap Rick, you are making my head hurt! That is amazing but when it comes to trouble shooting this it would become a problem.

Still, I IMPRESSED!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,712
Office Version
  1. 2010
Platform
  1. Windows
Oh crap Rick, you are making my head hurt! That is amazing but when it comes to trouble shooting this it would become a problem.

Still, I IMPRESSED!
Your head should be safe from pain as I did say not to use it. ;)

As for needing to trouble shoot it... that would not be a problem as the code works perfectly.:coffee:
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,295
Messages
5,600,780
Members
414,405
Latest member
Zaurb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top