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)

 

[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

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