Thread: Converting formula to UDF

Hello,
I have a quick question on how to propery convert this formula into a UDF.

Formula:

=IF(INT(C9*$D$5)+SUM(INT((C9*$D$5)/0.16))=0, ,INT(C9*$D$5)+SUM(INT((C9*$D$5)/0.16))&"# ")&IF(SUM(MOD((C9*$D$5), 0.16)*100)<=0, ,SUM(MOD((C9*$D$5), 0.16)*100)&"oz")

Here is my current code that is not working properly..

Function MyConvert(CellRef As String, CellWht As Double)
Dim Serving As Double
Dim ServTotal As Double
Dim ServInt As Integer
Dim Pound As Integer
Dim OZ As Integer
Serving = Range("D5").value
ServTotal = CellWht * Serving
ServInt = CellWht * Serving
If CellRef = "Weight" Then
    If ServTotal = 0 Then
    
    Else
    
    Pound = ServInt & "# "
    
        If ((ServTotal Mod 1) * 16) = 0 Then
            
            Else
            
            OZ = (ServTotal Mod 1) * 16 & "oz"
            
        End If

    End If
    
End If

MyConvert = Pound & OZ

End Function

Now for a short explanation, D5 is a serving size number, CellRef is just refrencing different ways of measurement, in this case it's Weight. CellWht would look like "1.04" which would be 1# 4oz for a serving size of 1 (which would be C9 in the formula).

It's ultimatley converting the serving amount depening on the serving size. So if D5 was changed to 2 (servings) 1.04 would not be 2.08 being 2# 8oz.

The formula works just fine, but if i ever need to make changes to any of them, i would have to go through them and change them on every worksheet, so i'm wanting a UDF so it's the matter of changing the UDF and every work sheet would be updated. Any help is much appreciated! Thank you very much!

Forget the formula for a minute. Explain what you want to do as clearly as you can. Also, attaching an example workbook would be useful.

Here is an example workbook...

It is for recipies, I have formula's wrote for all conversions, but it's for large scale. So the original recipe might have teaspoons, but when you change the serving size it would convert it to the correct measurements, rather than just showing 100 teaspoons, it would up convert it to show cups and table spoons etc. But if i can figure out how to write the UDF with weight, i should be able to rewrite the rest of them my self. The reason i want it in UDF and not forumla, is eventually this would have MANY worksheets, so if there is ever anything that needs to updated, i'd rather update one UDF than every worksheet being used...

So like i said in my original post, the forumla i have will convert 16oz into another pound, rather than showing oz..

In the example just adjust Serving size, and you will see how it works...

Here is another example that i didn't include in the above workbook... Here's tablespoon which both down converts and up converts...

=IF(SUM($C8*$D$5)<(1/3),MOD(($C8*$D$5*3*8),8)&" Pinches",IF(SUM($C8*$D$5)<1,SUM($C8*$D$5*3)&"Tsp",IF(SUM($C8*$D$5)<=4,SUM($C8*$D$5)&" T",SUM(INT(SUM(($C8*$D$5)/4))*0.25)&" Cup "&IF(MOD(SUM($C8*$D$5),4)=0,,MOD(SUM($C8*$D$5),4)&" T"))))

This formula is still a work in progress.. i don't have it completed

so it will down convert, going down to a pinch which is 1/8 of a tsp, 3 tsp is a tbsp, 16 tbspoons is a cup. But than it also does cups by the quarter, so 4 tbsp's being 1/4 cup, 8 being 1/2 etc.. But really what i need is the correct way to write one of these in UDF format... From there i should be able to figure the others out...

Col C is what the user would type in, so techincally col C and E would be hidden... C is the base receipe

But ultimatley most of the conversions i have completed or at least know how to do them in forumula, the problem I'm having is rewriting them in VBA format so they can work in a UDF.. Because theres conversions for.. Weight, Cup, TblSpn, Tsp, Pinch, Each, Gram, Kilogram, Column B is where they would specify what kind of measurement they are using for the BASE receipe... Hopefully I'm making some sense, i can upload the whole workbook if you would like, i was just trying to keep it simple...

Yep i completley aggree with just converting to metric, i wish i could. But not what the chef wants hah, but again, i have no problem writing the conversions and such, it's the converting it over to VBA format. So really, all i need is one of these wrote to VBA format, and from there, i can figure out the rest...

Here is my version of the linked recipe calculator. I realize that it does not address your question/request, but some might find it useful.