vba 如何通过VBA Excel中的函数获取单元格值

How to get the cells value through Function in VBA Excel

提问by R Sam

I am working on a task where the users enters first a column from “A” to “Z” and then a row number from 1 to 20. I have to write a function getIntData(col as String, row as Integer) as Integer that returns the content of that cell in the active worksheet and shows it in a message box. If the number is the maximum negative Integer show instead a message box with an exclamation mark. The function getIntData returns an Integer value. If the content of the cell is not numeric then return the maximum possible negative value (-2^15) i.e. -32768.

我正在执行一项任务，用户首先输入从“A”到“Z”的列，然后输入从 1 到 20 的行号。我必须编写一个函数 getIntData(col as String, row as Integer) as Integer返回活动工作表中该单元格的内容并将其显示在消息框中。如果数字是最大负整数，则显示带有感叹号的消息框。函数 getIntData 返回一个整数值。如果单元格的内容不是数字，则返回最大可能的负值 (-2^15)，即 -32768。

I have write some code for the function getIntdata() but it cann't produce integer value. Can anyone please look what I am missing. Moreover, please guide me how to run function through Sub procedure.

我已经为函数 getIntdata() 编写了一些代码，但它无法生成整数值。任何人都可以看看我缺少什么。此外，请指导我如何通过 Sub 程序运行函数。

 Sub getIn()

     Dim Row As Integer
     Dim Column As String
            Column = InputBox("Please enter the column letter from A-Z", "Column Letter")
            Row = InputBox("Please enter the row number from 1-20", "Row Number")
            Debug.Print getIntData(Column, Row)
 End Sub


Function getIntData(Col As String, Rw As Integer) As Integer
    Dim Result As Variant
      If Col = "" Or Rw < 1 Then
         Result = "Invalid inputs"
      ElseIf Not IsNumeric(Range(Col & Rw).Value) Then
         Result = -32768
      ElseIf Range(Col & Rw).Value <= -32768 Then
         Result = "!"
      ElseIf Range(Col & Rw).Value >= 32767 Then
         Result = "Now what?"
      Else
         Result = Range(Col & Rw).Value
      End If
      MsgBox Result
   ' return value to caller
         getIntData = Result
 End Function

回答by chris neilsen

You need getIntData = ...to return a value from the function

您需要getIntData = ...从函数返回一个值

You should also deal with a few other error cases

您还应该处理其他一些错误情况

Try this as a stater

试试这个作为状态

Function getIntData(Col As String, Rw As Integer) As Integer
    Dim Result As Variant
    If Col = "" Or Rw < 1 Then
        Result = "Invalid inputs"
    ElseIf Not IsNumeric(Range(Col & Rw).Value) Then
        Result = -32768
    ElseIf Range(Col & Rw).Value <= -32768 Then
        Result = "!"
    ElseIf Range(Col & Rw).Value >= 32767 Then
        Result = "Now what?"
    Else
        Result = Range(Col & Rw).Value
    End If
    MsgBox Result
    ' return value to caller
    getIntData = CInt(Result)
End Function

To call it from a Sub try

从子尝试调用它

Sub Demo()
    Debug.Print getIntData("A", 1)
End Sub