setting a variable

Gregfox

Board Regular
Joined
Apr 12, 2011
Messages
120
Hi I have the following:
Code:
Dim mySUN As Workbook
Set mySUN = Workbooks("c:\SUN\Get_Sun - 2018.xlsm")
I receive an error -->subscription out of range
I would like to correct this.
Thanks!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
If the workbook is open try
Code:
Set mySUN = Workbooks("Get_Sun - 2018.xlsm")
If its closed try
Code:
Set mySUN = Workbooks.open("c:\SUN\Get_Sun - 2018.xlsm")
 

Gregfox

Board Regular
Joined
Apr 12, 2011
Messages
120
Thank you that does work (excel 2007 / PC) the workbook is closed but I just wanted to declair it and use it later on in the program. Is there a way?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
Not if you want to declare it as a workbook object.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
You're welcome
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
If you're happy declaring your variables as string you could use something like
Code:
Dim Pth As String
Dim mySUN As String
Pth = "C:\SUN\"
mySUN = "Get_Sun - 2018.xlsm"

Workbooks.Open (Pth & Fname)
Workbooks(Fname).Sheets (1)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
Cross posted https://www.excelforum.com/excel-programming-vba-macros/1240456-setting-a-variable.html#post4948802

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

Forum statistics

Threads
1,170,969
Messages
5,873,050
Members
432,958
Latest member
Koloka

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