CODEWITHSUNDEEP
excelexcel-2010vba
supercheetah
supercheetah

Reputation: 3250

Excel VBA: Why can't I get a range from a worksheet I passed into a function?

I'm trying to use a helper function to get a range to store it into a variant, but I'm running into some issues.

At first, I just simply tried the following:

Function GetRange(RangeLetter As String, Optional LastUniqueLine As Long = 1048576) As Varient
    Static LastUniqueLineStr As String
    If LastUniqueLineStr = "" Then
        LastUniqueLineStr = LastUniqueLine
    End If
    Set GetRange = Range(RangeLetter + "2:" + LastUniqueLineStr)
End Function

But that didn't seem to work. Range() seemed to be out of scope here or something, so I figured I had to pass in the worksheet to get it working:

Function GetRange(RangeLetter As String, Optional LastUniqueLine As Long = 1048576, Optional ActiveSheet As Worksheet) As Variant
    Static LastUniqueLineStr As String
    Static CurrentSheet As Worksheet
    'If CurrentSheet = Nothing Then
    Set CurrentSheet = ActiveSheet
    'End If
    If LastUniqueLineStr = "" Then
        LastUniqueLineStr = LastUniqueLine
    End If
    Set GetRange = CurrentSheet.Range(RangeLetter + "2:" + LastUniqueLineStr) ' This is the line where I get the error.
End Function

And that's not working either. I get the following error:

Run-time error '1004':

Method 'Range' of object 'Worksheet' failed

How do I get the range I want out of this when I call it?

Upvotes: 0

Views: 1105

Answers (2)

Primiano19
Primiano19

Reputation: 21

It looks to me like the error is in the line where your setting Your "GetRange" variable. It looks like the result of .Range(RangeLetter + "2:" + Last....) would create a range with an address of "a2:#" which will fail. A range in that format needs to be "a2:e7" or similar. Your range reference has to be symmetrical. "RC:RC", "R:R", or "C:C". Hope that helps.

Upvotes: 1

David Zemens
David Zemens

Reputation: 53663

Try:

GetRange = Range(RangeLetter + "2:" + RangeLetter + LastUniqueLineStr).Value

You were missing a RangeLetter which was resulting in a malformed address. Also, use the .Value property to return a variant/array, and omit the Set keyword.

I continue to get the error when qualifying as CurrentSheet.Range... there is no ActiveSheet within the context of the Function, so you could pass a worksheet as a variable:

Sub Test()
Dim var As Variant
var = GetRange(ActiveSheet, "A")
End Sub

Function GetRange(sh As Worksheet, RangeLetter As String, Optional LastUniqueLine As Long = 1048576, Optional ActiveSheet As Worksheet) As Variant
    Static LastUniqueLineStr As String
    Dim myRange As Range

    If LastUniqueLineStr = "" Then
        LastUniqueLineStr = LastUniqueLine
    End If

    Set myRange = sh.Range(RangeLetter + "2:" + RangeLetter + LastUniqueLineStr)

    GetRange = myRange.Value ' This is the line where I get the error.
End Function

Upvotes: 1

Related Questions