Frustrating Excel VBA problem (Newbie with decades of experience, but apparently not enough)

[vanagas]

[FONT=&quot]This is excel 2019 being extended with vba. Trying some simple, nonsense first.... and then.... FRUSTRATION
the following code snippet is used as follows[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Cell #f21 has a value of tograms(a21,b21,16)[/FONT]
[FONT=&quot]A21 is typed as a numeric and has a value of 16[/FONT]
[FONT=&quot]B21 is typed as text and has a value of “oz”[/FONT]
[FONT=&quot]16 is a number[/FONT]
[FONT=&quot]The issue is:[/FONT]
[FONT=&quot]The first test should fail because the arg = “oz”, not “g”, but the if test always passes[/FONT]
[FONT=&quot]So that means i am doing something stupid (or rather not doing something i should be)[/FONT]
[FONT=&quot]I can’t believe i am stuck on such an elementary thing…[/FONT]
[FONT=&quot]FRUSTRATED[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Function ToGrams(CellQty As Double, CellUoM As String, CnvFactor As Double) As Double[/FONT]
[FONT=&quot]‘ This function converts cell contents to grams, as appropriate[/FONT]
[FONT=&quot]Dim VolConvert As Double[/FONT]
[FONT=&quot]If CellUoM = “g” Then ToGrams = Val(CellQty)[/FONT]
[FONT=&quot] Exit Function[/FONT]
[FONT=&quot]If Left(LCase(CellUoM), 2) = “gr” Then ToGrams = Val(CellQty)[/FONT]
[FONT=&quot] Exit Function[/FONT]
[FONT=&quot]If LCase(CellUoM) = “oz” Then ToGrams = Val(CellQty) * 28.3495[/FONT]
[FONT=&quot] Exit Function[/FONT]
[FONT=&quot]If Left(LCase(CellUoM), 2) = “ts” Or Left(LCase(CellUoM), 2) = “tb” Or Left(LCase(CellUoM), 2) = “c” Then VolConvert = Val(CnvFactor) * Val(CellQty)[/FONT]
[FONT=&quot]‘ ts will cover tsp[/FONT]
[FONT=&quot]‘ tb will cover tbs[/FONT]
[FONT=&quot]‘ c will cover cups[/FONT]
[FONT=&quot] If Left(LCase(CellUoM), 2) = “ts” Then ToGrams = VolConvert[/FONT]
[FONT=&quot] Exit Function[/FONT]
[FONT=&quot] If Left(LCase(CellUoM), 2) = “tb” Then ToGrams = VolConvert * 3[/FONT]
[FONT=&quot] Exit Function[/FONT]
[FONT=&quot] ToGrams = VolConvert * 48[/FONT]
[FONT=&quot] Exit Function[/FONT]
[FONT=&quot]If Left(LCase(CellUoM), 2) = “ml” Then ToGrams = Val(CnvFactor) * Val(CellQty) * 5[/FONT]
[FONT=&quot] Exit Function[/FONT]
[FONT=&quot]If Left(LCase(CellUoM), 4) = “drop” Then ToGrams = Val(CnvFactor) * Val(CellQty) / 100[/FONT]
[FONT=&quot] Exit Function[/FONT]
[FONT=&quot]If Left(LCase(CellUoM), 5) = “piece” Then ToGrams = Val(CnvFactor) * Val(CellQty)[/FONT]
[FONT=&quot] Exit Function[/FONT]
[FONT=&quot]End Function[/FONT]

 

[JLGWhiz]

I think you need to use Select Case or If...ElseIf...Then algorithms rather than the method you are trying to use. The Exit Function will kill whatever vlues your varibles have accumulated without transferring anything to the 'ToGrams'. So you would not get a result if any of the If statements are true.

 

[JLGWhiz]

Disregard post #2 . It will transfer the values when the If statement is true. Surprised me!

 

[Leith Ross]

Hello vanagas,

The problem I see is you are using the Val function incorrectly. This function is used to convert a text value into a Double number up to the first non numeric value. I removed these for you and used a Select Case to make the code more readable.

Function ToGrams(CellQty As Double, CellUoM As String, CnvFactor As Double) As Double


    ' This function converts cell contents to grams, as appropriate
    Dim VolConvert As Double


    UoM = LCase(UoM)
    
    Select Case UoM
        Case Is = "g", "gr": ToGrams = CellQty
        Case Is = "oz": ToGrams = CellQty * 28.3495
        Case Is = "ts", "tb", "c": VolConvert = CnvFactor * CellQty
            If UoM = "ts" Then ToGrams = VolConvert
            If UoM = "tb" Then ToGrams = VolConvert * 3
            If UoM = "c" Then ToGrams = VolConvert * 48
        Case Is = "ml": ToGrams = CnvFactor * CellQty * 5
        Case Is = "drop": ToGrams = CnvFactor * CellQty / 100
        Case Is = "piece": ToGrams = CnvFactor * CellQty
    End Select


End Function

 

[JoeMo]

As your code stands now, if CellUoM is anything other than "g" you exit the function. You need a construct like this:

Function ToGrams(CellQty As Double, CellUoM As String, CnvFactor As Double) As Double
'‘ This function converts cell contents to grams, as appropriate
Dim VolConvert As Double
If CellUoM = “g” Then
    ToGrams = Val(CellQty)
    Exit Function
End If
If Left(LCase(CellUoM), 2) = “gr” Then
    ToGrams = Val(CellQty)
    Exit Function
End If
If CellUoM = “oz” Then
    ToGrams = Val(CellQty) * 28.3495
    Exit Function
End If
' and so on

However, beyond that there seems to be something problematic with your code. For example, if I copy your posted code and paste it to my VBE, this test

If LCase(CellUoM) = “oz” Then
    ToGrams = Val(CellQty) * 28.3495
    Exit Function
End If

fails to recognize "oz" in cell B21 (the CellUoM arg). When I simply re-type the pasted opening line: If LCase(CellUoM)= "oz" then it returns the expected value and exits function. There may be other sources of corruption. I would be inclined to re-type the function in a new workbook and test it again.

 

[vanagas]

As your code stands now, if CellUoM is anything other than "g" you exit the function. You need a construct like this:

Function ToGrams(CellQty As Double, CellUoM As String, CnvFactor As Double) As Double
'‘ This function converts cell contents to grams, as appropriate
Dim VolConvert As Double
If CellUoM = “g” Then
    ToGrams = Val(CellQty)
    Exit Function
End If
If Left(LCase(CellUoM), 2) = “gr” Then
    ToGrams = Val(CellQty)
    Exit Function
End If
If CellUoM = “oz” Then
    ToGrams = Val(CellQty) * 28.3495
    Exit Function
End If
' and so on

However, beyond that there seems to be something problematic with your code. For example, if I copy your posted code and paste it to my VBE, this test

If LCase(CellUoM) = “oz” Then
    ToGrams = Val(CellQty) * 28.3495
    Exit Function
End If

fails to recognize "oz" in cell B21 (the CellUoM arg). When I simply re-type the pasted opening line: If LCase(CellUoM)= "oz" then it returns the expected value and exits function. There may be other sources of corruption. I would be inclined to re-type the function in a new workbook and test it again.

Thanks everyone for your comments and corrections. It was late (for me, at least) and the idea of code getting the better of me was not going to allow any restful shut-eye. So I apologize for not responding right away, but the the corrections and observations listed herein were very valuable and we are now past this dumb hump. Thanks again.
vanagas