OK, I can't get this function to return anything other than #VALUE! What am
I doing wrong? The function is in a module, and the input parameter is a
reference to a cell containing an integer.

Function my_digit_patt(a) As Integer
Dim ct(9) As Integer
For i = 1 To Len(a)
ct(Val(Mid(a, i, 1))) = ct(Val(Mid(a, i, 1))) + 1
Next
b = 0
For i = 0 To 10
b = b + ct(i) * 10 ^ (10 - i)
Next
my_digit_patt = b
End Function

Don,

This seem to work...
'---------------------------
Function my_digit_patt(ByRef a As Variant) As Double
Dim i As Long
Dim b As Double
Dim ct(0 To 9) As Long

For i = 1 To Len(a)
ct(Val(Mid(a, i, 1))) = ct(Val(Mid(a, i, 1))) + 1
Next 'i

b = 0
For i = 0 To Len(a)
b = b + ct(i) * 10 ^ (10 - i)
Next 'i

my_digit_patt = b
End Function
'---'

Sub GetIt()
MsgBox my_digit_patt(110)
End Sub

'-------------------------------------
Jim Cone
San Francisco, USA


"Don Shepherd" wrote in
message ...
OK, I can't get this function to return anything other than #VALUE! What am
I doing wrong? The function is in a module, and the input parameter is a
reference to a cell containing an integer.
Function my_digit_patt(a) As Integer
Dim ct(9) As Integer
For i = 1 To Len(a)
ct(Val(Mid(a, i, 1))) = ct(Val(Mid(a, i, 1))) + 1
Next
b = 0
For i = 0 To 10
b = b + ct(i) * 10 ^ (10 - i)
Next
my_digit_patt = b
End Function

On Fri, 10 Jun 2005 21:55:17 -0400, "Don Shepherd" wrote:

OK, I can't get this function to return anything other than #VALUE! What am
I doing wrong? The function is in a module, and the input parameter is a
reference to a cell containing an integer.

Function my_digit_patt(a) As Integer
Dim ct(9) As Integer
For i = 1 To Len(a)
ct(Val(Mid(a, i, 1))) = ct(Val(Mid(a, i, 1))) + 1
Next
b = 0
For i = 0 To 10
b = b + ct(i) * 10 ^ (10 - i)
Next
my_digit_patt = b
End Function


Well, I see a few problems off the top of my head.

1. On line 7: For i = 0 To 10
You are using i as the subscript for ct. But the upper bound of ct is
only 9. So when i=10, the subscript will be out of range.

2. You have specified that your function should return an integer. But the
allowable range for integers in VBA is -32,768 to 32,767. Depending on the
range of values being passed to the function, the result may be out of range of
your data type.


--ron

Hey, thanks Jim and Ron, your help is much appreciated. Yes, I noticed the
out of range subscript after I posted and fixed it, but the problem
persisted. The real culprit, as you suggested, was using integer data type
when I wanted a 10 digit integer result. I switched to double and it worked
like a charm.

Thanks again guys, you are a lifesaver!

"Don Shepherd" wrote in message
...
OK, I can't get this function to return anything other than #VALUE! What
am
I doing wrong? The function is in a module, and the input parameter is a
reference to a cell containing an integer.

Function my_digit_patt(a) As Integer
Dim ct(9) As Integer
For i = 1 To Len(a)
ct(Val(Mid(a, i, 1))) = ct(Val(Mid(a, i, 1))) + 1
Next
b = 0
For i = 0 To 10
b = b + ct(i) * 10 ^ (10 - i)
Next
my_digit_patt = b
End Function