Array Formula creates error if too may cell chosen

[ExcelMonkey]

When using a UDF in Excel as an Array formula (CTRL-ALT-DELETE), how do you
error handle to avoid getting #N/A in cell ranges which are larger than
results of the array. See example below.

Thanks


Function MyVowelCountArray(r As String) As Variant
Dim VowelCountArray As Variant
'Dim r As String
'This counts the number of vowels
'in a string and returns the found
'vowels in an array

'For example type in "alphabet" in A1
'Then in B2:E2 type in MyVowelCountArray($A$1)
'and hit CTRL-ALT-DELETE
'you will see "a" "a" "e" "#N/A" in these cells

ReDim VowelCountArray(0 To 0)

Count = 0
For i = 1 To Len(r)
Ch = UCase(Mid(r, i, 1))
If Ch Like "[AEIOU]" Then
Count = Count + 1
ReDim Preserve VowelCountArray(0 To Count - 1)
VowelCountArray(Count - 1) = Ch
End If
Next i
MyVowelCountArray = VowelCountArray
End Function

[Kletcho]

You could try using the iserror statement in your formula:

=if(iserror(MyVowelCountArray($A$1),"",MyVowelCountArray($A$1))

[Bob Phillips]

Function MyVowelCountArray(r As String) As Variant
Dim VowelCountArray As Variant
'Dim r As String
'This counts the number of vowels
'in a string and returns the found
'vowels in an array

'For example type in "alphabet" in A1
'Then in B2:E2 type in MyVowelCountArray($A$1)
'and hit CTRL-ALT-DELETE
'you will see "a" "a" "e" "#N/A" in these cells

ReDim VowelCountArray(0 To 0)

Count = 0
For i = 1 To Len(r)
Ch = UCase(Mid(r, i, 1))
If Ch Like "[AEIOU]" Then
Count = Count + 1
ReDim Preserve VowelCountArray(0 To Count - 1)
VowelCountArray(Count - 1) = Ch
End If
Next i
If Selection.Count <= i Then
i = UBound(VowelCountArray) + 1
ReDim Preserve VowelCountArray(0 To Selection.Count - 1)
For i = i To Selection.Count - 1
VowelCountArray(i) = ""
Next i
MyVowelCountArray = VowelCountArray
End If
End Function

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"ExcelMonkey" <ExcelMonkey@discussions.microsoft.com> wrote in message
news:10C99653-9CAC-429B-921F-338FA19F64DE@microsoft.com...
> When using a UDF in Excel as an Array formula (CTRL-ALT-DELETE), how do
you
> error handle to avoid getting #N/A in cell ranges which are larger than
> results of the array. See example below.
>
> Thanks
>
>
> Function MyVowelCountArray(r As String) As Variant
> Dim VowelCountArray As Variant
> 'Dim r As String
> 'This counts the number of vowels
> 'in a string and returns the found
> 'vowels in an array
>
> 'For example type in "alphabet" in A1
> 'Then in B2:E2 type in MyVowelCountArray($A$1)
> 'and hit CTRL-ALT-DELETE
> 'you will see "a" "a" "e" "#N/A" in these cells
>
> ReDim VowelCountArray(0 To 0)
>
> Count = 0
> For i = 1 To Len(r)
> Ch = UCase(Mid(r, i, 1))
> If Ch Like "[AEIOU]" Then
> Count = Count + 1
> ReDim Preserve VowelCountArray(0 To Count - 1)
> VowelCountArray(Count - 1) = Ch
> End If
> Next i
> MyVowelCountArray = VowelCountArray
> End Function

[Dave Peterson]

First, it's ctrl-shift-enter (not alt-ctrl-delete):

And this seemed to work ok for me:

Option Explicit
Function MyVowelCountArray(r As String) As Variant
Dim VowelCountArray() As String
Dim myCount As Long
Dim i As Long
Dim Ch As String
Dim MaxCells As Long

MaxCells = Application.Caller.Cells.Count
ReDim VowelCountArray(1 To MaxCells)

myCount = 0
For i = 1 To Len(r)
Ch = UCase(Mid(r, i, 1))
If Ch Like "[AEIOU]" Then
myCount = myCount + 1
If myCount > MaxCells Then
Exit For 'not enough room
End If
VowelCountArray(myCount) = Ch
End If
Next i

If myCount > MaxCells Then
MyVowelCountArray = CVErr(xlErrRef)
Else
For i = myCount + 1 To MaxCells
VowelCountArray(i) = ""
Next i
MyVowelCountArray = VowelCountArray
End If

End Function




ExcelMonkey wrote:
>
> When using a UDF in Excel as an Array formula (CTRL-ALT-DELETE), how do you
> error handle to avoid getting #N/A in cell ranges which are larger than
> results of the array. See example below.
>
> Thanks
>
> Function MyVowelCountArray(r As String) As Variant
> Dim VowelCountArray As Variant
> 'Dim r As String
> 'This counts the number of vowels
> 'in a string and returns the found
> 'vowels in an array
>
> 'For example type in "alphabet" in A1
> 'Then in B2:E2 type in MyVowelCountArray($A$1)
> 'and hit CTRL-ALT-DELETE
> 'you will see "a" "a" "e" "#N/A" in these cells
>
> ReDim VowelCountArray(0 To 0)
>
> Count = 0
> For i = 1 To Len(r)
> Ch = UCase(Mid(r, i, 1))
> If Ch Like "[AEIOU]" Then
> Count = Count + 1
> ReDim Preserve VowelCountArray(0 To Count - 1)
> VowelCountArray(Count - 1) = Ch
> End If
> Next i
> MyVowelCountArray = VowelCountArray
> End Function

--

Dave Peterson