UDF to work per row

[Roderick_E]

So I've created this cool rating UDF for my office which I thought was going to calculate per row but for some reason it repeats the calculation of whatever row I press ENTER on.

I've applied it to a column and dragged down =basiscalc()

The code should be getting the active row of each place the UDF is used...I thought. How do I fix to make it work per row? See especially actrow = ActiveCell.Row 'get active cell row
Thanks


CODE:

Function basiscalc(Optional calctype As String)
Application.Volatile
Dim baserate, minrate, maxrate As Double
Dim cur, basis As String
Dim actrow As Long
Dim precalc As Double
On Error Resume Next
'get columns positions
actrow = ActiveCell.Row 'get active cell row
basecol = ActiveSheet.Range("basecol").Column
basiscol = ActiveSheet.Range("basiscol").Column
curcol = ActiveSheet.Range("curcol").Column
mincol = ActiveSheet.Range("mincol").Column
maxcol = ActiveSheet.Range("maxcol").Column
baserate = Cells(actrow, basecol)
minrate = Cells(actrow, mincol)
maxrate = Cells(actrow, maxcol)
cur = Cells(actrow, curcol)
basis = Cells(actrow, basiscol)
'get totals
totalkgs = ActiveSheet.Range("totalkgs")
totallbs = ActiveSheet.Range("totallbs")
totalcbm = ActiveSheet.Range("totalcbm")
totalhbl = ActiveSheet.Range("totalhbl")
totalplt = ActiveSheet.Range("totalplt")
'do basis
precalc = baserate
'*LBS*
If basis = "lbs" Or basis = "pounds" Then
If calctype = "byrow" Then
precalc = precalc * rowlbs
Else
precalc = precalc * totallbs
End If
End If
'*KGS*
If basis = "kg" Or basis = "kgs" Or basis = "kilo" Or basis = "kilos" Or basis = "kilograms" Then
If calctype = "byrow" Then
precalc = precalc * rowkgs
Else
precalc = precalc * totalkgs
End If
End If
'*CBM*
If basis = "cbm" Or basis = "cbms" Or basis = "cubic meters" Or basis = "cubicmeters" Or basis = "m3" Then
If calctype = "byrow" Then
precalc = precalc * rowcbm
Else
precalc = precalc * totalcbm
End If
End If
'*WM*
If basis = "wm" Or basis = "w/m" Or basis = "weight/measure" Or basis = "w-m" Or basis = "wm." Then
If calctype = "byrow" Then
precalc = precalc * Application.Max(rowwm, rowkgs / 1000)
Else
precalc = precalc * Application.Max(totalwm, totalkgs / 1000)
End If
End If




'Flat
If basis = "flat" Or basis = "" Then
precalc = precalc
End If


'do min and max
precalc = Application.Max(precalc, minrate) 'minumum
If maxrate > 0 Then
precalc = Application.Min(baserate, maxrate) 'maximum
End If
basiscalc = precalc
Resume Next


End Function

 

[shg]

application.caller.row (.column) gives the position of the calling cell.

But Excel sees no dependency on the inputs the UDF trolls for, which is why you had to make it volatile. That's not good practice.

 

[Roderick_E]

application.caller.row (.column) gives the position of the calling cell.

But Excel sees no dependency on the inputs the UDF trolls for, which is why you had to make it volatile. That's not good practice.

Any suggestions for a better practice? Thanks BTW

 

[shg]

Sure: Pass all the arguments the function requires to return a result.

 

[Roderick_E]

Sure: Pass all the arguments the function requires to return a result.

In this case just that active row I believe -- I'll give it a try Thanks

 

[shg]

I'd do something like this, though it should be able to be simplified.

Function basiscalc(basis As String, baserate As Double, _
                   totalkgs As Double, totallbs As Double, totalwm As Double, _
                   totalcbm As Double, totalhbl As Double, totalplt As Double, _
                   minrate As Double, maxrate As Double)
  Select Case basis
    Case "lbs", "pounds"
      basiscalc = baserate * totallbs

    Case "kg", "kgs", "kilo", "kilos", "kilograms"
      basiscalc = baserate * totalkgs

    Case "cbm", "cbms", "cubic meters", "cubicmeters", "m3"
      basiscalc = baserate * totalcbm

    Case "wm", "w/m", "weight/measure", "w-m", "wm."
      If totalwm > totalkgs / 1000 Then
        basiscalc = baserate * totalwm
      Else
        basiscalc = baserate * totalkgs / 1000
      End If
    
    Case "flat", ""
      basiscalc = baserate
  End Select

  If basiscalc < minrate Then basiscalc = minrate
  If maxrate > 0 Then If basiscalc > maxrate Then basiscalc = maxrate
End Function

 

[Roderick_E]

I'd do something like this, though it should be able to be simplified.

Function basiscalc(basis As String, baserate As Double, _
                   totalkgs As Double, totallbs As Double, totalwm As Double, _
                   totalcbm As Double, totalhbl As Double, totalplt As Double, _
                   minrate As Double, maxrate As Double)
  Select Case basis
    Case "lbs", "pounds"
      basiscalc = baserate * totallbs

    Case "kg", "kgs", "kilo", "kilos", "kilograms"
      basiscalc = baserate * totalkgs

    Case "cbm", "cbms", "cubic meters", "cubicmeters", "m3"
      basiscalc = baserate * totalcbm

    Case "wm", "w/m", "weight/measure", "w-m", "wm."
      If totalwm > totalkgs / 1000 Then
        basiscalc = baserate * totalwm
      Else
        basiscalc = baserate * totalkgs / 1000
      End If
    
    Case "flat", ""
      basiscalc = baserate
  End Select

  If basiscalc < minrate Then basiscalc = minrate
  If maxrate > 0 Then If basiscalc > maxrate Then basiscalc = maxrate
End Function

Hmmm Don't know if this will work since the column or cell where the weight & measures are located could be dynamic. I need a way to determine that. I've been testing my original version with your add application.caller.row and it is working perfectly. Why is it not a good practice?

 

[shg]

Because Excel doesn't see any dependency on the input cells; because the procedure has to go looking for its arguments; because no one else would understand how it works.

If you wrote a SIN function, would you pass no arguments and have it return the sine of the value two cells to the left?