VBA - Transfer a Range of a sheet to a function

Question

I can't manage to transfer the full name of a Range to a function.

I have 2 sheets : WS1 and WS2.

In a Sub, I find various ranges related to WS1 and WS2. But when I debug, their coordinates are not preceded by the name of the sheet:

Public Function myFunction(ByRef amountRange As Range)
....
End Function






Sub Testons()
'
Dim nb As Integer, lastrow As Integer,
Dim WS1 As Worksheet, WS2 As Worksheet
 With ThisWorkbook
        Set WS1 = .Worksheets("WS1"): Set WS2 = .Worksheets("WS2")
    End With

    With Worksheets("WS1")
        nb = .Range("A2", .Range("A2").End(xlDown)).Rows.Count
        lastrow = nb + 1
        Set amountRange = WS1.Range("C2:C" & lastrow)
'THIS RESULTS IN 'WS1'!$C$2:$C$9 AND NOT IN 'WS1'!$C$2:$C$9 (what I want)
    End With
    result = myFunction(amountRange)
End Sub

Consequently, my function can't do what I want. How to have a Range preceded by the worksheet name eg 'WS1'!$C$2:$C$9 ?

Not clear what you're after. 'THIS RESULTS IN 'WS1'!$C$2:$C$9 AND NOT IN 'WS1'!$C$2:$C$9 (what I want) - they're both the same result. Dim nb As Integer, lastrow As Integer - these will cause overflow errors, change them to long. AmountRange holds a reference to C2:Cxxxx on the WS1 sheet. To get the exact string you're after use "'" & amountrange.Parent.name & "'!" & amountrange.Address — Darren Bartrup-Cook
I assume in Testons() you're declaring amountRange as a Range? You're passing the Range itself to myFunction, so you need to look at what you're doing with it there. — CLR

h2so4 h2so4 · Accepted Answer · 2017-03-27T14:53:40

This is because VBA handles references to cells in another way than excel does.

you can find the coordinates of the range with

range.address

and the sheet where the cells belongs to with

range.parent.name.

VBA - Transfer a Range of a sheet to a function

2 Answers