How do you count strings within an array?

Thanks

Do you mean count the number of entries that are strings? If so, I guess you
have to loop through checking the type.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" wrote in message
...
How do you count strings within an array?

Thanks

I simply have an array loaded with strings only. If I use
Ubound and there are empty elements in the array, will the
Ubound pick up the empties too. I do not want to count
the empties.


-----Original Message-----
Do you mean count the number of entries that are strings?
If so, I guess you
have to loop through checking the type.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" wrote
in message
...
How do you count strings within an array?

Thanks


.

Yes it will, because Ubound is the upper bound, whatever it has been
dimensioned to. Actually, as LBound could also be any value, the correct
number is UBound((Ary)-LBound(ary)+1

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" wrote in message
...
I simply have an array loaded with strings only. If I use
Ubound and there are empty elements in the array, will the
Ubound pick up the empties too. I do not want to count
the empties.


-----Original Message-----
Do you mean count the number of entries that are strings?
If so, I guess you
have to loop through checking the type.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" wrote
in message
...
How do you count strings within an array?

Thanks


.

Hi,

I simply have an array loaded with strings only. If I use
Ubound and there are empty elements in the array, will the
Ubound pick up the empties too. I do not want to count
the empties.

n = Application.WorksheetFunction.CountA(YourArray)

Regards,

Daniel M.

This surprises me, in a satisfying way ;-).

You have to take it away from the array size to count the strings, and it
fails if the array contains another array, or an object, but interesting.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Daniel.M" wrote in message
...
Hi,

I simply have an array loaded with strings only. If I use
Ubound and there are empty elements in the array, will the
Ubound pick up the empties too. I do not want to count
the empties.

n = Application.WorksheetFunction.CountA(YourArray)

Regards,

Daniel M.

Define count strings. How many elements contain a string? If so, how do
you declare the array?

--
Regards,
Tom Ogilvy

"ExcelMonkey" wrote in message
...
How do you count strings within an array?

Thanks

Given an array, you want to count the numberof rows that are NOT empty:
correct?

Try:

Function xx(ByVal StringArray As Variant) As Integer
StringArray = Join(StringArray, ",")
While 0 < InStr(StringArray, ",,")
StringArray = Replace(StringArray, ",,", ",")
Wend
StringArray = Split(StringArray, ",")
xx = UBound(StringArray)
End Function

"ExcelMonkey" wrote:

How do you count strings within an array?

Thanks

Interesting approach. The "old fashioned" way is:

Function NonEmpty(StringArray() AS String) AS Long
Dim i As Long
Dim N AS Long
For i = LBound(StringArray) to UBound(StringArray)
If Len(StringArray(i)) Then N = N + 1
Next i
NonEmpty = N
End Function

For a 35-element array with 15 empty and 20 filled, the above runs in
approximately 2/3 of the time (87 vs 150 microsec). String operations like
join, replace, and split are expensive time-wise. Getting the string length
amounts to a simple look-up.


On Tue, 15 Mar 2005 05:17:04 -0800, "AA2e72E"
wrote:

Given an array, you want to count the numberof rows that are NOT empty:
correct?

Try:

Function xx(ByVal StringArray As Variant) As Integer
StringArray = Join(StringArray, ",")
While 0 < InStr(StringArray, ",,")
StringArray = Replace(StringArray, ",,", ",")
Wend
StringArray = Split(StringArray, ",")
xx = UBound(StringArray)
End Function

"ExcelMonkey" wrote:

How do you count strings within an array?

Thanks

I'm not sure, but would this idea work?

Sub Demo()
Dim v As Variant
Dim n As Long
Dim p As Long
Const k As String = "String"

v = Array(1, 2, "aa", "bb", 3, 4, WorksheetFunction.Pi())
For p = LBound(v) To UBound(v)
n = n + 1 - Abs(StrComp(TypeName(v(p)), k))
Next p
Debug.Print "Number of Strings: "; n
End Sub

- Number of Strings: 2

--
Dana DeLouis
Win XP & Office 2003


"ExcelMonkey" wrote in message
...
How do you count strings within an array?

Thanks