单元格引用在Excel VBA 2010功能中不起作用 [英] Cells reference not working in Excel VBA 2010 Function

问题描述

我正在尝试编写一个需要一系列数据的函数，并计算用户提供的列中两个数字的比例。我想在行的末尾打印这个比例，但由于某种原因，我无法使用单元格函数引用行中的最后一个单元格。相反，单元格功能只是向我提供该单元格的值，而不是单元格地址。我认为单元格功能也提供了地址。有人可以告诉我，如果我的代码错了吗？

这里是代码

 函数calculateRatio（表As Range，numerator As Integer，分母As Integer，可选nameOfRatio As String）
错误GoTo ExpectedError 
 Dim num As Double 
 Dim denom As Double 
 Dim ratio As Double 
 
如果table.Columns.Count< 2然后
 MsgBox（数据不足，至少需要两行或更多行）
退出函数
结束如果
如果分子< 1或分子> table.Columns.Count然后
 MsgBox（不可接受的分子，必须大于零而小于& table.Columns.Count）
退出函数
如果
如果分母< 1或分母> table.Columns.Count然后
 MsgBox（不可接受的分母，必须大于零而小于& table.Columns.Count）
退出函数
如果
对于Counter = 1 To table.Rows.Count 
 num = table.cells（计数器，分子）
 denom = table.cells（计数器，分母）
 ratio = num / denom 
 temp = table.cells（counter，table.columns.count）
 temp.Offset（0，1）.Value = ratio 
下一个计数器
 
退出函数
 ExpectedError：
调用MsgBox（发生错误，请确保您引用了带数字而不是文本的列。& Err.Number&：& Err.Description）
结束
结束功能
  

更新

这是更新的代码：

 函数calculateRatio（表As Range，分子As Integer，分母As Integer，可选nameOfRatio As String）
 Dim num作为Double 
 Dim denom As Double 
 Dim ratio As Double 
 Dim temp As Range 
 Dim counter As Integer 
 
如果table.Columns.Count< 2然后
 MsgBox（数据不足，至少需要两行或更多行）
退出函数
结束如果
如果分子< 1或分子> table.Columns.Count然后
 MsgBox（不可接受的分子，必须大于零而小于& table.Columns.Count）
退出函数
如果
如果分母< 1或分母> table.Columns.Count然后
 MsgBox（不可接受的分母，必须大于零而小于& table.Columns.Count）
退出函数
如果
对于计数器= 1到table.Rows.Count 
 num = table.cells（计数器，分子）
 denom = table.cells（计数器，分母）
 ratio = num / denom 
设置temp = table.cells（counter，table.Columns.Count）
 temp.Offset（0，1）.Value = ratio 
下一个计数器
结束函数
  

解决方案

无法从工作表中调用的UDF操作工作表单元格。

更多信息：

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

而这里：

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

一般来说，子程序可以操作工作表，功能不能。

异常是从子例程中调用的函数可以，但是除了向子例程返回值之外的任何东西，使用函数可能是一个不好的习惯。 p>

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

解决方案

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.

这篇关于单元格引用在Excel VBA 2010功能中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！