从函数返回范围 [英] Returning a Range from a Function

问题描述

我试图在VBA中调用另一个函数，返回一个范围并设置为一个变量。我尝试运行GetInputs（）方法时出现语法错误。

 函数GetDataRange（str As String）As Range 
'这提示用户选择一个数据范围，我们需要调用一次输入，一次输出
 Dim rRange As Range 
 On Error Resume Next 
应用程序.DisplayAlerts = False 
设置rRange = Application.InputBox（提示：= _ 
 str，_ 
标题：=SPECIFY RANGE，类型：= 8）
错误GoTo 0 
 Application.DisplayAlerts = True 
如果rRange不是，然后
退出函数
 Else 
 rRange.Font.Bold = True 
 End If 
 GetDataRange = rRange 
结束函数
 
 Sub GetInputs（）
 Dim rg As Range 
设置rg = GetDataRange（选择输入：）
 
 
 End Sub 
  

编辑：我添加了这段代码：

  Sub Test（）
 
 End Sub 
  

当我尝试运行它，我得到了相同的Syntax错误与Sub Test（）行突出显示。

解决方案

乍一看，你缺少在范围赋值语句中设置关键字：

GetDataRange = rRange

相反，它应该是：

设置GetDataRange = rRange

已更新我已经运行代码，这是我观察到的唯一错误。这应该解决它。

个人而言，我将避免在您的函数体内执行此操作：

  rRange.Font.Bold = True 
  

而是把它放在你的通话例程：

  Sub GetInputs（）
 Dim rg as Range 
 Set rg = GetDataRange（Select Inputs ：）
如果不是rg不是，那么rg.Font.Bold = True 
如果
  

这样，你主要使用该函数来获取一个返回值，而不是对对象执行操作。但这主要是偏好的问题。

I am trying to have a sub in VBA call another function, which returns a range and is set to a variable. I am getting a syntax error when I try to run the GetInputs() method.

Function GetDataRange(str As String) As Range
' This prompts the user to select a range of data, we will need to call this once for inputs and once for outputs
Dim rRange As Range
 On Error Resume Next
  Application.DisplayAlerts = False
    Set rRange = Application.InputBox(Prompt:= _
        str, _
        Title:="SPECIFY RANGE", Type:=8)
 On Error GoTo 0
  Application.DisplayAlerts = True
  If rRange Is Nothing Then
   Exit Function
  Else
   rRange.Font.Bold = True
  End If
  GetDataRange = rRange
End Function

Sub GetInputs()
 Dim rg As Range
 Set rg = GetDataRange("Select Inputs:")


End Sub

Edit: I added this code:

Sub Test()

End Sub

When I try to run it I get the same Syntax error with the Sub Test() line highlighted.

解决方案

First glance, you're missing the Set keyword in your range assignment statement:

GetDataRange = rRange

Instead, it should be:

Set GetDataRange = rRange

Updated I have run the code and this was the only error I observe. This should fix it.

Personally, I would avoid doing this inside your function body:

rRange.Font.Bold = True

And instead, put it in your calling routine:

Sub GetInputs()
Dim rg as Range
    Set rg = GetDataRange("Select Inputs:")
    If Not rg Is Nothing Then rg.Font.Bold = True
End If

This way, you use the function primarily to get a return value, not to perform operations on an object. But that is a matter of preference mostly.

这篇关于从函数返回范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！