Two UDF Functions with the same name

[andreascostas]

OK. I have two UDF functions both named the same thing: :Decompose", each one decomposes a number
but in different format. Now i need to use both functions in the same worksheet, but excel won't let me do that.
How do I go about doing that.
Here are the functions:

Function Decompose(ByVal Number As String) As String
Dim X As Long
Number = Replace(Number, ",", "")
If Number = 0 Then
Decompose = "0 ones"
Else
For X = Len(Number) To 1 Step -1
If Mid(Number, X, 1) Then Decompose = Mid(Number, X, 1) & Choose(Len(Number) - X + 1, " ones", " tens", " hundreds", " thousands", " ten thousands", " hundreds thousands") & " + " & Decompose
Next
Decompose = Left(Decompose, Len(Decompose) - 3)
End If
End Function

second function

Function Decompose(ByVal Number As String) As String
Dim X As Long
Number = Replace(Number, ",", "")
If Number = 0 Then
Decompose = "0 ones"
Else
For X = Len(Number) To 1 Step -1
If Mid(Number, X, 1) Then Decompose = Mid(Number, X, 1) & Choose(Len(Number) - X + 1, " x 1)", " x 10)", " x 100)", " x 1,000)", " x 10,000)", " x 100,000)") & " + " & Decompose
Next
Decompose = Left(Decompose, Len(Decompose) - 3)
End If
End Function

 

[Trebor76]

Hi andreascostas,

Have one function but pass in a second parameter which will determine which If statement to use based on what logic you have used to split the UDF's.

HTH

Robert

 

[andreascostas]

I am not sure I know how to do that. What is a second parameter? And where do I put in?

 

[Trebor76]

Just like now you pass in a number for the function to work, you can pass another one. Have a look at the following where I'd added a boolean variable to determine how the number is displayed:

Function Decompose(ByVal Number As String, blnNumeric As Boolean) As String
    Dim X As Long
    Number = Replace(Number, ",", "")
    If Number = 0 Then
        Decompose = "0 ones"
    Else
        If blnNumeric = False Then
            For X = Len(Number) To 1 Step -1
                If Mid(Number, X, 1) Then Decompose = Mid(Number, X, 1) & Choose(Len(Number) - X + 1, " ones", " tens", " hundreds", " thousands", " ten thousands", " hundreds thousands") & " + " & Decompose
            Next X
        ElseIf blnNumeric = True Then
            For X = Len(Number) To 1 Step -1
                If Mid(Number, X, 1) Then Decompose = Mid(Number, X, 1) & Choose(Len(Number) - X + 1, " x 1)", " x 10)", " x 100)", " x 1,000)", " x 10,000)", " x 100,000)") & " + " & Decompose
            Next X
        End If
    Decompose = Left(Decompose, Len(Decompose) - 3)
    End If
End Function

So you would use:
=Decompose(526333,FALSE) or =Decompose(526333,0) to display the number as 5 hundreds thousands + 2 ten thousands + 6 thousands + 3 hundreds + 3 tens + 3 ones or use:
=Decompose(526333,TRUE) or =Decompose(526333,1) to display it as 5 x 100,000) + 2 x 10,000) + 6 x 1,000) + 3 x 100) + 3 x 10) + 3 x 1)

Regards,

Robert

 

[andreascostas]

Well, I just replaced the word "decompose" in one of the functions. This way I was able to heve both of them in the same workbook.
It worked. Thank you for your help.

 

[Trebor76]

Yes that will work but you essentially have two identical functions bar one line which seems like a lot of wastage to me. I would personally use one function with two parameters as I've shown above but it's up to you.

I'm glad you got it sorted.

 

[Trebor76]

This is even more succinct:

Function Decompose(ByVal Number As String, blnNumeric As Boolean) As String
    Dim X As Long
    Number = Replace(Number, ",", "")
    If Number = 0 Then
        Decompose = "0 ones"
    Else
        For X = Len(Number) To 1 Step -1
            If blnNumeric = False Then
                If Mid(Number, X, 1) Then Decompose = Mid(Number, X, 1) & Choose(Len(Number) - X + 1, " ones", " tens", " hundreds", " thousands", " ten thousands", " hundreds thousands") & " + " & Decompose
            ElseIf blnNumeric = True Then
                If Mid(Number, X, 1) Then Decompose = Mid(Number, X, 1) & Choose(Len(Number) - X + 1, " x 1)", " x 10)", " x 100)", " x 1,000)", " x 10,000)", " x 100,000)") & " + " & Decompose
            End If
        Next X
    End If
    Decompose = Left(Decompose, Len(Decompose) - 3)
End Function

 

[Rick Rothstein]

This is even more succinct:

Function Decompose(ByVal Number As String, blnNumeric As Boolean) As String
    Dim X As Long
    Number = Replace(Number, ",", "")
    If Number = 0 Then
        Decompose = "0 ones"
    Else
        For X = Len(Number) To 1 Step -1
            If blnNumeric = False Then
                If Mid(Number, X, 1) Then Decompose = Mid(Number, X, 1) & Choose(Len(Number) - X + 1, " ones", " tens", " hundreds", " thousands", " ten thousands", " hundreds thousands") & " + " & Decompose
            ElseIf blnNumeric = True Then
                If Mid(Number, X, 1) Then Decompose = "(" & Mid(Number, X, 1) & Choose(Len(Number) - X + 1, " x 1)", " x 10)", " x 100)", " x 1,000)", " x 10,000)", " x 100,000)") & " + " & Decompose
            End If
        Next X
    End If
    Decompose = Left(Decompose, Len(Decompose) - 3)
End Function

You omitted the opening parenthesis shown in red above. Also, I think there is something wrong with your output for Number equal to 0, but I did not attempt to fix it.

With that said, here is an even more succinct version of the Decompose function...

[table="width: 500"]
[tr]
	[td]Function Decompose(ByVal Number As String, blnNumeric As Boolean) As String
  Dim X As Long, Txt As Variant
  Txt = Split("0 ones,ones,tens,hundreds,thousands,ten thousands,hundred thousands,0 x 1,x 1,x 10,x 100,x 1|000,x 10|000,x 100|000", ",")
  Number = Replace(Number, ",", "")
  For X = Len(Number) To 1 Step -1
    If Val(Mid(Number, Len(Number) - X + 1, 1)) = 0 Imp Len(Number) = 1 Then Decompose = Decompose & " + " & IIf(blnNumeric, "(", "") & Mid(Number, Len(Number) - X + 1, 1) & " " & Txt(X - 7 * blnNumeric) & IIf(blnNumeric, ")", "")
  Next
  Decompose = Replace(Mid(Decompose, 4), "|", ",")
End Function[/td]
[/tr]
[/table]

 

[Trebor76]

Hi Rick,

That's how the OP had posted the original code which I just tried to incorporate into a single UDF. Thanks for posting your solution.

Regards,

Robert

 

[Rick Rothstein]

Thanks Rick

You are quite welcome. Just wondering... did you see the second comment I made about your code regarding the output for 0 (I made the change at the last minute maybe 3 or 4 minutes before you posted, so it may have crossed with your post "in the ether")?