Returning a Range from a Function

Question

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.

David Zemens David Zemens · Accepted Answer · 2014-04-30T01:15:27

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.

Returning a Range from a Function

1 Answers