Trouble with worksheet variable

[davidam]

Hello All,
I am using the following to set a worksheet variable

Public thisBook As Workbook
Public thisSheet As Worksheet
 
Set thisBook = ThisWorkbook
Set thisSheet = thisBook.Worksheets("Data Input")
Call Save_Case

Later I try to use it:

Dim testval As Integer
testval = thisSheet.Range("Q376").Value

I get run time error: Object variable or With block variable not set. This one I do not understand at all. I set the variables as above and then try to use them in the procedure called Save_Case all in the same module...is it perhaps something to do with using ThisWorkbook?

 

[Norie]

Where are the declarations?

To properly declare public variables they should be declared outside any sub.

By the way, and I think I might have asked this before, why are you using public variables?

 

[davidam]

The declarations are at the top of a module...not inside a sub. I am using public variables because I do reference them in other modules.

 

[Norie]

Have you considered passing these variables to the other modules/subs as arguments?

Here's a very simple example.

Module 1:

Option Explicit
 
Sub SecondOne(ws As Worksheet)
 
    ws.Name = "NewName"
    
End Sub

Module2:

Option Explicit

Sub TheFirstone()
Dim ws1 As Worksheet

    Set ws1 = Worksheets("Sheet1")
    
    Call SecondOne(ws1)
    
End Sub

The sub FirstOne calls the sub SecondOne and passes worksheet ws1 as an argument.

SecondOne then renames that worksheet.

 

[davidam]

I am finding you example a bit hard to follow, but are you saying that arguments would be used instead of Public variables?

 

[Norie]

I'm saying that passing arguments could be used instead of public variables.

If you use public variables you need to be pretty careful.

In particular you need to take into consideration of the scope and lifetime of the variables.

What do you find hard to follow in the code I posted?

It's just a very simple example of how to pass something between subs using arguments.

In VBA you do it all the time when using methods like Find, Workbooks.Open etc.

 

[davidam]

So the SecondOne is setting a variable called ws.Name which is then used by FirstOne...essentially the same as making it public because you can access the variable from anywhere by calling it...but without the problems. I get the concept.

What I do not understand in your example is the connection between ws and ws1. Aso, when you call SecondOne, why does it have ws1 in the brackets

Sorry to be so thick

 

[Norie]

I'm sorry for asking again, but why are you using public variables in the first place?

I don't think you need to, and it just seems to be complicating things.

 

[xenou]

What I do not understand in your example is the connection between ws and ws1. Aso, when you call SecondOne, why does it have ws1 in the brackets

It's basically just how you enclose arguments that are required by another sub, but VBA is a little confusing because sometimes you pass arguments without using parentheses.

Your first example should have worked but with publics you can lose the variable for certain reasons (a common one being anytime you hit reset or compile your code - so it's particularly likely to happen while developing and writing code).

 

[davidam]

I am using public variables because I did not know any better...I obviously have to study up on passing variables as arguments. Anyone have a good link on the subject for a newbie?