Setting a Workbook Variable's Value from Another Variable

AnyOldName

New Member
Joined
May 18, 2019
Messages
8
Is there a way toset the value of a workbook variable from another variable that holds a string?

I already have oneaction that does this by using ActiveWorkbook ,as shown below, but I have twoother workbooks that is are data templates, one of which will be opened up bythe macro.

Public Var_Code_WorkbookName As Workbook


Set Var_Code_WorkbookName = ActiveWorkbook
Debug.Print "DRPS Code Workbook is: " & Var_DRPS_Code_WorkbookName.FullName

Is there a way to do the same thing but referencing the value of a string rather than using ActiveWorkbook?



Public Var_Data_Template1 As Workbook
Public Var_Template_Type As String 'This variable holds the file path & filename of the data template workbook, those values are picked up from elsewhere in the code
'eg C:\User\VBA Code\ Excel File.xlsx


Set Var_Data_Template1 = Var_Template_Type

I can't figure out a method or the syntax for this last line, can anyone advise if its possible? Thankyou
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,383
Office Version
  1. 2019
Platform
  1. Windows
Hi,
welcome to forum

you use Set to assign an object reference to your variable in this case, the workbooks object.

If your string variable contains the path & filename and the workbook is not open you would do something like this

Code:
Var_Template_Type = "C:\User\VBA Code\Excel File.xlsx"
Set Var_Data_Template1 = Workbooks.Open(Var_Template_Type)

If the workbook is already open you just need to include the workbook name in your string variable.

Code:
Var_Template_Type = "Excel File.xlsx"
Set Var_Data_Template1 = Workbooks(Var_Template_Type)

Dave
 

AnyOldName

New Member
Joined
May 18, 2019
Messages
8
Thanks for the welcome and the reply, that little section is working perfectly now. I'm pretty sure I'd already tried it this way as I had that exact line of code for setting the variable in my sub but set as a comment and indented several times, which is how I keep old code that hasn't worked while trying to problem solve but nevermind, its working now.

Two following questions from this are:

Q1) How do I use the the workbook variable I've just defined to drive the code to name a variable for a worksheet within in that same workbook?

Currently I am doing this:

Public Var_Template_Setup_Sheet as Worksheet

Set Var_Template_Setup_Sheet = ActiveWorkbook.Worksheets("Set Up -DO NOT RENAME")

But I would prefer to do it with variables so that I'm not reliant on having the correct workbook / worksheet active. eg, I want to to something like this:

Set Var_Template_Setup_Sheet = Workbooks(Var_Data_Template1).Worksheets("Set Up -DO NOT RENAME")


Q2) Can anyone recommend a decent reference to help me learn the finer points of this type of stuff? So far I'm self taught from Walkenbach's Excel VBA Programming for Dummies and looking around forums such as this but my core understanding of the method and syntax isn't as strong as I'd like it to be.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,383
Office Version
  1. 2019
Platform
  1. Windows
But I would prefer to do it with variables so that I'm not reliant on having the correct workbook / worksheet active. eg, I want to to something like this:

Set Var_Template_Setup_Sheet = Workbooks(Var_Data_Template1).Worksheets("Set Up -DO NOT RENAME")

Code:
Set Var_Data_Template1 = ActiveWorkbook


Set Var_Template_Setup_Sheet = Var_Data_Template1.Worksheets("Set Up -DO NOT RENAME")



Q2) Can anyone recommend a decent reference to help me learn the finer points of this type of stuff? So far I'm self taught from Walkenbach's Excel VBA Programming for Dummies and looking around forums such as this but my core understanding of the method and syntax isn't as strong as I'd like it to be.

Just do a search, plenty of information out there like this: http://www.cfo.com/spreadsheets/201...er-worksheets-or-workbooks-in-an-excel-macro/

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

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