Using Array Formulas in VBA

[DHS100]

Hi,

I have a VBA Array that holds various sentences. I need to know how many words are in the entire array. If this were on a normal spreadsheet I'd use the formula:

{=(SUM(LEN(Test))-SUM(LEN(SUBSTITUTE(Test," ",""))))+COUNTA(Test)}

How would I do this on a VBA array?

Many thanks

 

[Norie]

What exactly do you mean by a 'VBA array'?

 

[dcardno]

If the VBA array is Words(), with each array entry holding a string, you could try (air code)

for i = 1 to ubound(words)
if len(Words(i)) > 0 then
iCount = iCount + Len(Words(i)) - Len(worksheetfunction.substitute(Words(i)," ","")) + 1
end if
next i
...

I am not sure if there is a VBA equivalent of "substitute" - if so, that will probably be faster; adapt the lower bound of Words() depending on your option base setting.