0

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.

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
atomant
  • 339
  • 1
  • 2
  • 9

1 Answers1

4

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.

David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • Ok, I think my problem is something else then. I have added the Set statement back in and I get the same error. In fact, I added an empty Sub called Test() and I tried running it and still got the same Syntax Error. – atomant Apr 30 '14 at 01:23
  • If you're getting a syntax error, then the VBE should be highlighting which line is causing the error. Please update your original question to include the code you're currently using :) – David Zemens Apr 30 '14 at 01:30
  • Or indicate which line is raising the error. I can probably help. – David Zemens Apr 30 '14 at 01:31
  • I edited my post, the Sub Test() line is highlighted – atomant Apr 30 '14 at 01:35
  • Ok, I just cut the 3 functions from the top of the page to the bottom and now it is working... – atomant Apr 30 '14 at 01:50
  • OK. well if it is working, you should accept this answer. It's possible you had some other code that was a problem... – David Zemens Apr 30 '14 at 02:35