Change UDF to work as array formula

[Jan Kronsell]

I have the following UDF. It count the number of any digit in a cell

Function NDIG(ce As String, no As Byte) As Variant
Dim cif(9) As Long
For i = 1 To Len(ce)
Select Case CByte(Mid(ce, i, 1))
Case Is = 0
cif(0) = cif(0) + 1
Case Is = 1
cif(1) = cif(1) + 1
Case Is = 2
cif(2) = cif(2) + 1
Case Is = 3
cif(3) = cif(3) + 1
Case Is = 4
cif(4) = cif(4) + 1
Case Is = 5
cif(5) = cif(5) + 1
Case Is = 6
cif(6) = cif(6) + 1
Case Is = 7
cif(7) = cif(7) + 1
Case Is = 8
cif(8) = cif(8) + 1
Case Is = 9
cif(9) = cif(9) + 1
End Select
Next
NDIG = cif(no)
End Function

I use i by typing the number I want counted in a1 and the numbers fro, 0 to
9 in B1:B10. I then enter the UDF in C1 like =NDIG($A$1;B1) and copy down to
C10, and it retunrs the number of any digit in the cell.

Now I wonder if the UDF can be changes, so I can enter it as an array
formula in C1:C10 in stead of having to copy it?

Jan

 

[Dave Peterson]

How about just selecting 10 contiguous cells (10 rows x 1 column or 1 row by 10
columns) and have the count of the numbers returned into that range.

Option Explicit
Function NDIG(myStr As String) As Variant
Dim cif(0 To 9) As Long
Dim iCtr As Long
Dim myChar As String

For iCtr = 1 To Len(myStr)
myChar = Mid(myStr, iCtr, 1)
If IsNumeric(myStr) Then
cif(CLng(myChar)) = cif(CLng(myChar)) + 1
End If
Next iCtr

If Application.Caller.Rows.Count = 10 _
And Application.Caller.Columns.Count = 1 Then
NDIG = Application.Transpose(cif)
ElseIf Application.Caller.Rows.Count = 1 _
And Application.Caller.Columns.Count = 10 Then
NDIG = cif
Else
NDIG = CVErr(xlErrRef)
End If

End Function


Select b1:B10 and array enter (ctrl-shift-enter) your formula:
=NDIG(a1)

 

[Jan Kronsell]

Great. Thank you.

Jan

How about just selecting 10 contiguous cells (10 rows x 1 column or 1
row by 10 columns) and have the count of the numbers returned into
that range.

Option Explicit
Function NDIG(myStr As String) As Variant
Dim cif(0 To 9) As Long
Dim iCtr As Long
Dim myChar As String

For iCtr = 1 To Len(myStr)
myChar = Mid(myStr, iCtr, 1)
If IsNumeric(myStr) Then
cif(CLng(myChar)) = cif(CLng(myChar)) + 1
End If
Next iCtr

If Application.Caller.Rows.Count = 10 _
And Application.Caller.Columns.Count = 1 Then
NDIG = Application.Transpose(cif)
ElseIf Application.Caller.Rows.Count = 1 _
And Application.Caller.Columns.Count = 10 Then
NDIG = cif
Else
NDIG = CVErr(xlErrRef)
End If

End Function


Select b1:B10 and array enter (ctrl-shift-enter) your formula:
=NDIG(a1)