Excel: Variabel for Sheets?

[asedt]

I'm making a macro in Excel.

In my macro i have something like this in many places:

Sheet1.Range(Col & Row).Value
Sheet2.Range(Col & Row).Value
 

I whant variables for the Sheets so I can change them in one place and don't need to run a replace on them in the hole module.

Something like

Dim mySheet1 ' as ? _ mySheet1 = "Sheet1"
Dim mySheet2 ' as ? _ mySheet2 = "Sheet2"
mySheet1.Range(Col & Row).Value
mySheet2.Range(Col & Row).Value
 

Tryied some different things but can't get it working, can you do it by using right thig at "as ?" or can you do it any other way?

[FishVal]

Hello.

You may easily get an answer yourself.
Toggle breakpoint anywhere within the scope of the variable.
In VBE immediate window type:

? TypeName(<...variable name...>)

You will get the variable type.

Also you may add variable to Watch window and get plenty of info about it.

Also I strongly recommend you to use Object Browser (F2 in VBE) to view classes available in referenced libraries.

Kind regards,
Valentine

[asedt]

In VBE immediate window type:

? TypeName(<...variable name...>)

You will get the variable type.

Hi

Thanks dident know about that "Interpreter window" before.

I did as you said and got "Worksheet" as answer.

So the declaration is:

Dim mySheet As Worksheet
 

But then the assignment I don't know how to do, tryied all of this but don't get it working.

mySheet = ActiveWorkbook.Worksheets(1)
mySheet = ActiveWorkbook.Sheets(1)
mySheet = ActiveWorkbook.Worksheets.Item(1)
mySheet = ActiveWorkbook.Sheets.Item(1)
 

Tryed with <"Sheet1">, <"1"> insted of <1> at index, nothing working. Tryed to find answers in the Object browser and Whatch window but culdn't find anything about assignment of Worksheets.

[FishVal]

Use "Set" operator to assign a value to object variable.

Set objVar1 = objVar2

[asedt]

Use "Set" operator to assign a value to object variable.

Set objVar1 = objVar2

Now it works, thanks :)

Hmm newer used set when assigning Integers or Strings so i didn't now about that you needed to use it for "object variables".

[FishVal]

You are welcome.

Regards,
Fish

P.S. BTW there are two operators in Basic to assign value to variable. "Let" is being used for non-object variables and "Set" is used for object ones. "Let" is assumed by default, so usually it is omitted.