By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,987 Members | 1,607 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,987 IT Pros & Developers. It's quick & easy.

Excel: Variabel for Sheets?

100+
P: 125
I'm making a macro in Excel.

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

Expand|Select|Wrap|Line Numbers
  1. Sheet1.Range(Col & Row).Value
  2. Sheet2.Range(Col & Row).Value
  3.  
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
  1. Dim mySheet1 ' as ? _ mySheet1 = "Sheet1"
  2. Dim mySheet2 ' as ? _ mySheet2 = "Sheet2"
  3. mySheet1.Range(Col & Row).Value
  4. mySheet2.Range(Col & Row).Value
  5.  
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?
Sep 3 '08 #1
Share this Question
Share on Google+
5 Replies


FishVal
Expert 2.5K+
P: 2,653
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
Sep 3 '08 #2

100+
P: 125
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
  1. Dim mySheet As Worksheet
  2.  
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
  1. mySheet = ActiveWorkbook.Worksheets(1)
  2. mySheet = ActiveWorkbook.Sheets(1)
  3. mySheet = ActiveWorkbook.Worksheets.Item(1)
  4. mySheet = ActiveWorkbook.Sheets.Item(1)
  5.  
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.
Sep 4 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Use "Set" operator to assign a value to object variable.

Set objVar1 = objVar2
Sep 4 '08 #4

100+
P: 125
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".
Sep 4 '08 #5

FishVal
Expert 2.5K+
P: 2,653
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.
Sep 4 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.