Excel: Variabel for Sheets?

asedt

I'm making a macro in Excel.

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

Expand|Select|Wrap|Line Numbers

 
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

Expand|Select|Wrap|Line Numbers

 
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

Quote:

Originally Posted by FishVal

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:

Expand|Select|Wrap|Line Numbers

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.

Expand|Select|Wrap|Line Numbers

 
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

Quote:

Originally Posted by FishVal

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.

Excel: Variabel for Sheets?

re: Excel: Variabel for Sheets?

re: Excel: Variabel for Sheets?

re: Excel: Variabel for Sheets?

re: Excel: Variabel for Sheets?

re: Excel: Variabel for Sheets?

/bytes/development

/bytes/it

/bytes/about