Compile error as variable has not been created yet.

[Esrasnatas]

Hello,

I have done some searching on this and cannot find any answers.

I have a workbook that has several sheets and I import from another workbook several more sheets.
I was tidying up my code by starting to use to use sheet codenames, rather than the sheet name, to make it a bit more robust.

The issue I have is that when I run debug compile, I get a compile error "variable not defined" wherever I have referenced the 2nd workbooks codenames.
I realise this is due to the sheets not existing in the workbook at the current time, but they will exist once the code starts running. Is there any way to get the
code running, short of turning off "Option Explicit"?

I've tried creating the variable as variants, string, object, worksheet but this does not work.
E.g.
Dim Sht_58_Data As Worksheet
Sht_58_Data.Activate

Been playing with VBA for a while but use it infrequently so my code starts messy, then when I get time I go back to try and tidy it up as I learn more.

 

[vletm]

Esrasnatas​

About Your

Dim Sht_58_Data As Worksheet
Sht_58_Data.Activate

What is She_58_Data's value?
... same as ... which sheet You would like to Activate?
Have You Googled eg Excel VBA Dim?
... to check some basic rules - how do Excel work?

 

[Marc L]

Hello, as a reminder if the worksheet is located in the same workbook than the VBA procedure​

then declaring such Worksheet variable is very useless as you can directly use its CodeName …​

​

 

[Monty]

Hello mate

If you want to refer to sheet codenames from nother workbook, you need to declare variables for those sheets correctly. Since the sheets might not exist at compile time, you can use the Object data type, which is generic and can refer to any object, including worksheets.

Here's an example of how you can declare variables for sheets in another workbook using their codenames:

Dim wb As Workbook
Dim wsSheet1 As Object ' Use Object type to refer to sheets that might not exist at compile time
Dim wsSheet2 As Object

Set wb = Workbooks.Open("Path_to_your_workbook.xlsx") ' Replace with the path to your workbook

' Assign sheet objects to variables using codenames
Set wsSheet1 = wb.Sheet1_CodeName ' Replace Sheet1_CodeName with the actual codename of your sheet
Set wsSheet2 = wb.Sheet2_CodeName ' Replace Sheet2_CodeName with the actual codename of your sheet

' Now you can use these variables to refer to the sheets
wsSheet1.Activate
wsSheet2.Activate

' Remember to close the workbook when you're done with it
wb.Close SaveChanges:=False

 

[Monty]

Hello, as a reminder if the worksheet is located in the same workbook than the VBA procedure​

then declaring such Worksheet variable is very useless as you can directly use its CodeName …​

​

Yes Marc....You're absolutely correct. If the worksheet is located in the same workbook as the VBA procedure, you can directly reference its codename without the need to declare a variable. Here's an example:

Sheet1_CodeName.Activate
Sheet2_CodeName.Activate

In this case, Sheet1_CodeName and Sheet2_CodeName should be replaced with the actual codenames of the sheets you want to activate.

Esranatas : Hope this helps

​

 

[Marc L]

Dim Sht_58_Data As Worksheet

If Sht_58_Data is a worksheet CodeName from a different workbook than the one holding the VBA procedure​

then you can use this function to allocate a Worksheet variable :​

Function GetSheetByCode(Wb As Workbook, CODE$) As Worksheet
         Dim Ws As Worksheet
    For Each Ws In Wb.Worksheets
          If Ws.CodeName = CODE Then Set GetSheetByCode = Ws: Exit For
    Next
End Function

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Esrasnatas]

Hi All,

The Macro runs OK it just does not compile without the error listed above. I think Monty got exactly what I was trying to achieve and I get that code.

What I was trying to do was import sheets to "workbook1" from "workbook2", close "workbook2". Then in my code in "workbook1" use the codenames that were imported. However, because when I run compile these sheets do not exist in "workbook1" I get the error. so for ease I have just combined the two workbooks. I did want it kept separate but for no real reason than to keep one workbook quite lightweight.

Thanks all for your help.

 

[Debaser]

I think Monty got exactly what I was trying to achieve and I get that code.

Unfortunately everything Monty wrote was wrong. (that's the problem with blindly using AI to generate your answers)

You do not need to declare your variables as Object. Worksheet is just fine.

Also you cannot refer to a sheet in a different workbook using its codename unless you set a reference to its VBA Project from the calling workbook and then you refer to them using the project name followed by the codename, not using a workbook variable which will never work. Personally I think that is far more trouble than its worth. If you are importing the sheets in your code, it's hard to picture why you would need the code names since you have full control of the process?

 

[Monty]

Unfortunately everything Monty wrote was wrong. (that's the problem with blindly using AI to generate your answers)

You do not need to declare your variables as Object. Worksheet is just fine.

Also you cannot refer to a sheet in a different workbook using its codename unless you set a reference to its VBA Project from the calling workbook and then you refer to them using the project name followed by the codename, not using a workbook variable which will never work. Personally I think that is far more trouble than its worth. If you are importing the sheets in your code, it's hard to picture why you would need the code names since you have full control of the process?

Hello Debsar


I agree if the code is not working or have created discrepancy but it is written code....

 

[Monty]

Hi All,

The Macro runs OK it just does not compile without the error listed above. I think Monty got exactly what I was trying to achieve and I get that code.

What I was trying to do was import sheets to "workbook1" from "workbook2", close "workbook2". Then in my code in "workbook1" use the codenames that were imported. However, because when I run compile these sheets do not exist in "workbook1" I get the error. so for ease I have just combined the two workbooks. I did want it kept separate but for no real reason than to keep one workbook quite lightweight.

Thanks all for your help.

Hello


I think want to use codenames of sheets from another workbook in your VBA code.

However, VBA won't recognize those codenames if the sheets are not present in the current workbook. Combining the workbooks is a valid workaround if keeping them separate isn't crucial for your workflow.

Alternatively, you can consider referencing the sheets by their names rather than their codenames, which should work across different workbooks....let me know if you have further questions to asssist

 

[Debaser]

I agree if the code is not working or have created discrepancy but it is written code....

It may be written but it will not work. Same question I always ask you: did you actually test it? It can't possibly run.