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?

 

[xenou]

I'd suggest Excel VBA Programming for Dummies by Walkenbach - any version. Excellent introduction. You could in fact read or use any introduction to programming or any intro tutorial for any programming language. Passing arguments is a general concept that applies to any programming language. In other words, you probably need a Programming 101 course.

A good way to learn VBA is also to know and use your debugging skills - you can walk through the program line by line and literally watch what the variables are doing as you step through the code:
http://krgreenlee.blogspot.com/2006/04/programming-excel-vba-debugging-for.html

 

[davidam]

Thanks!
David