Cells reference not working in Excel VBA 2010 Function

1
votes

I am attempting to write a function that takes a range of data and calculates the ratio of two numbers from the columns provided by the user. I want to print this ratio out at the end of the row, but for some reason I am not able to reference the last cell in the row using the cells function. Instead, the Cells function just keeps providing me the value of that cell and not the cell address. I thought that the cells function provides the address as well. Can someone tell me if this wrong or if my code is wrong?

Here is the code

Function calculateRatio(table As Range, numerator As Integer, denominator As Integer,       Optional nameOfRatio As String)
On Error GoTo ExpectedError
    Dim num As Double
    Dim denom As Double
    Dim ratio As Double

    If table.Columns.Count < 2 Then
        MsgBox ("Not enough data. Requires at least two or more rows.")
        Exit Function
    End If
    If numerator < 1 Or numerator > table.Columns.Count Then
        MsgBox ("Not an acceptable Numerator. Must be greater than zero and less than " & table.Columns.Count)
        Exit Function
    End If
    If denominator < 1 Or denominator > table.Columns.Count Then
        MsgBox ("Not an acceptable Denominator. Must be greater than zero and less than " & table.Columns.Count)
     Exit Function
     End If
    For Counter = 1 To table.Rows.Count
        num = table.cells(Counter, numerator)
        denom = table.cells(Counter, denominator)
        ratio = num / denom
        temp = table.cells(counter, table.columns.count)
        temp.Offset(0, 1).Value = ratio
     Next Counter

 Exit Function
ExpectedError:
    Call MsgBox("Something went wrong. Make sure you are referencing columns with numbers and not text." & Err.Number & " : " & Err.Description)
End
End Function

UPDATE

Here is the updated code:

Function calculateRatio(table As Range, numerator As Integer, denominator As Integer, Optional nameOfRatio As String)
    Dim num As Double
   Dim denom As Double
   Dim ratio As Double
   Dim temp As Range
   Dim counter As Integer

    If table.Columns.Count < 2 Then
        MsgBox ("Not enough data. Requires at least two or more rows.")
        Exit Function
    End If
    If numerator < 1 Or numerator > table.Columns.Count Then
        MsgBox ("Not an acceptable Numerator. Must be greater than zero and less than " & table.Columns.Count)
        Exit Function
    End If
    If denominator < 1 Or denominator > table.Columns.Count Then
         MsgBox ("Not an acceptable Denominator. Must be greater than zero and less than " & table.Columns.Count)
        Exit Function
    End If
    For counter = 1 To table.Rows.Count
        num = table.cells(counter, numerator)
        denom = table.cells(counter, denominator)
        ratio = num / denom
        Set temp = table.cells(counter, table.Columns.Count)
        temp.Offset(0, 1).Value = ratio
    Next counter
End Function
3
excelexcel-2010vba
Are you trying to call this function as a UDF from within the Worksheet? - David Zemens
I am trying to call it as a UDF within the worksheet. Will this cause an issue? - derigible
You're not able to manipulate worksheet cells using a UDF. - David Zemens
Can this only be done using subroutines? I have been able to manipulate worksheet cells using subroutines before. - derigible
Yes, it can be done using subroutines. See my answer below for some links that explain in a bit more detail. But generally, subroutines can manipulate the worksheet, and functions cannot. (Functions called from within a subroutine can, however this is probably a bad habit to use a Function for anything other than returning values to the Subroutine). - David Zemens

3 Answers

1
votes

It is not possible to manipulate worksheet cells from a UDF called from the worksheet.

More information here:

https://stackoverflow.com/a/15647054/1467082

And here:

http://www.excel-it.com/UDF.htm

generally, subroutines can manipulate the worksheet, and functions cannot.

The exception is that functions called from within a subroutine can, however this is probably a bad habit to use a function for anything other than returning values to the Subroutine.

1
votes

Try adding Dim temp as Range and chagne temp = table.cells(counter, table.columns.count) to set temp = table.cells(counter, table.columns.count)

Really all you need to do is add the set, without it the Variant temp is becoming a long, with it the Variant would become a Range object.

0
votes

I'm assuming you're not using Option Explicit in your code.

You need to declare "temp" as a range.

Dim temp As Range ' somewhere at the top of your function

For Counter = 1 To table.Rows.Count
......
Set temp = table.cells(Counter, table.columns.count)
...
Next

If you've got the cell coordinates why not offset it manually?

table.cells(counter, table.Columns.Count+1).Value = ratio

Try this:

Function calculateRatio(table As Range, numerator As Integer, denominator As Integer, Optional nameOfRatio As String)
    Dim num As Double
   Dim denom As Double
   Dim ratio As Double
   Dim temp As Range
   Dim counter As Integer

    If table.Columns.Count < 2 Then
        MsgBox ("Not enough data. Requires at least two or more rows.")
        Exit Function
    End If
    If numerator < 1 Or numerator > table.Columns.Count Then
        MsgBox ("Not an acceptable Numerator. Must be greater than zero and less than " & table.Columns.Count)
        Exit Function
    End If
    If denominator < 1 Or denominator > table.Columns.Count Then
         MsgBox ("Not an acceptable Denominator. Must be greater than zero and less than " & table.Columns.Count)
        Exit Function
    End If
    For counter = 1 To table.Rows.Count
        num = table.cells(counter, numerator)
        denom = table.cells(counter, denominator)
        ratio = num / denom
        table.cells(counter, table.Columns(table.Columns.Count).Column + 1).Value = ratio
        'Set temp = table.cells(counter, table.Columns.Count)
        'temp.Offset(0, 1).Value = ratio
    Next counter
End Function

Using `table.columns(table.columns.count).column will make sure that your referencing the correct column, cant think of an example that would cause problems at the moment but better to be safe.