setting a worksheet object as module variable, getting error

1
votes

This is what I am trying:

  1. In module, declare module-scope variables for worksheet object;
  2. In ThisWorkbook object, set the variables when workbook opens.

The code for each:

Module 1:

Option Explicit
Public ws As Worksheet


Function func()
    dim rng as Range
    set rng = ws.Range("namedRange")
    'rest of code ...
end function

ThisWorkbook:

Private Sub Workbook_Open()
    Set ws = Worksheets("Sales")
End Sub

When I open the workbook, the first run throws an error:

Method 'Range' of object '_Worksheet' failed

and all times after that, the error is:

Object variable or With block variable not set

1
vbaexcel
I wasn't able to duplicate your problem. See this image for how I've set up the project. What line of code highlights when you get the error? - Automate This
you need to set rng = ws.Range... - KekuSemau
Me too. I tried your code, and it works fine. Are you sure that is the only code in your Workbook_Open event? If yes, I see no reason for it not to work. - L42

1 Answers

1
votes

I also tried your code. And I almost reached the decision that there is no problem!

But after some trial 'the second error' appeared. My observation is:

When you add any extra sub or fn or reset your vba code, that public object variable get destroyed and you can no longer access it.

FYI: Reference link