CODEWITHSUNDEEP
excelvba
Eptagon
Eptagon

Reputation: 68

VBA Worksheet Variable

I can't seem to use Worksheet variables as I'd expect them to.

Function Test()
    Test = Application.ThisCell.Worksheet.Range("A1").Value
End Function

The above function works as expected, returning the value of cell "A1" in the same worksheet as the cell the function was called from.

Function Test()
    Dim ws As Worksheet
    ws = Application.ThisCell.Worksheet
    Test = ws.Range("A1").Value
End Function

This one should be functionally the same, but breaks and returns #VALUE! instead.

I can make do with the former, but, in formulae that require it several times, I'd rather only get the worksheet location once.

Any input on why my second example doesn't work and how to fix my issue would be welcome.

Upvotes: 1

Views: 114

Answers (1)

Damian
Damian

Reputation: 5174

When using WorksheetVariables you must use Set .

In your case Set ws = Thisworkbook.Sheets("NameOfTheSheet")

Thought... you are trying to set the worksheet using some variable, i would Hardcode the name or use a loop to get it.

Upvotes: 3

Related Questions