Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
setting object variable
i am looking for some advice on object variables. I have a module with
two sub routines in it. i have the following object variables at module level dim codes as worksheets i want to use this variable in both subs, and the value(worksheet) will be the same, so do i have to set the variable in both subs, set codes = worksheets("sheet1) this is what i am doing at the moment but i am not sure if this is correct or good programming. regards chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
setting object variable
I guess the first question is why you want an object variable set
to Worksheets since you already have the built-in Worksheets objects. That said, you can write code like Dim Codes As Sheets Set Codes = ThisWorkbook.Worksheets Debug.Print Codes("Sheet1").Name -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com wrote in message oups.com... i am looking for some advice on object variables. I have a module with two sub routines in it. i have the following object variables at module level dim codes as worksheets i want to use this variable in both subs, and the value(worksheet) will be the same, so do i have to set the variable in both subs, set codes = worksheets("sheet1) this is what i am doing at the moment but i am not sure if this is correct or good programming. regards chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
setting object variable
For referencing sheets you can do that directly by renaming the Code Name for
the sheet. In the properties window of the VBE (with Sheet1 selected) you will see the first listed property is (Name). Change this from Sheet1 to shtMySheet. In your code you can now reference the sheet directly. Sheets("Sheet1").Select 'Note intelisense does not work here with "." shtMysheet.Select 'Note intelisense does work here with "." will do the exact same thing. The difference is that if the user changes the tab name from Sheet1 to something else your code will crash. Using shtMySheet your code will be fine because the user can not change the code name (unless the go to the VBE). In your example you have declared codes as worksheets when I think you meant worksheet. You can pass a worksheet as arguments from one procedure to the next. HTH " wrote: i am looking for some advice on object variables. I have a module with two sub routines in it. i have the following object variables at module level dim codes as worksheets i want to use this variable in both subs, and the value(worksheet) will be the same, so do i have to set the variable in both subs, set codes = worksheets("sheet1) this is what i am doing at the moment but i am not sure if this is correct or good programming. regards chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Object Variable Not Set Error on Selection object | Excel Worksheet Functions | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming | |||
Pivot Table - Object variable or with block variable not set? | Excel Programming |