Creating Dynamic Worksheet Object Variables

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,021
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
VBA Code:
Dim ws_crp as worksheet
Dim ws_cwp as worksheet

x = "CRP"
    Set ws_{x} = activeworkbook.worksheets(x)

Is it possible to build a variable name? I'm not quite sure of the VBA syntax to do so.

I want to build dynamic worksheet object variables based on the value of x. So if the value of x = "CRP", the object variable ws_crp would be created referring to worksheet("CRP"). If x = "CWP", the object variable ws_cwp would be created and set to refer to worksheet("CWP").
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,582
Office Version
  1. 2013
Platform
  1. Windows
VBA does not provide the option to declare variables at run-time. There are ways to simulate this, but which way is the most suitable depends on both the situation and what you intend to do with it. I'm thinking of using an Array or a Dictionary. That being said, I don't see the need for dynamic run-time variable names, especially when using object variables. A well-considered naming of your worksheets, for example, often offers broader possibilities, comparable to the dynamics you are looking for.
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,021
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thank you. I will revisit my logic and see what kind of damage I can inflict lol.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,582
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and hopefully the damage will be limited 😎
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,576
Messages
5,765,196
Members
425,266
Latest member
CPAgirl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top