Code Module in Sheets Collection

[CG]

I am looping through all sheets in a workbook and come across a sheet
that is named the same a one of the code modules and is
xlsheetveryhidden. I changed the code module name with VBE and re-ran
the code. The name changed to the new name. How could this be
possible?

I believe this is a very old workbook. Could this have anything to do
with macrosheets? The sheet does not have a codename.

 

[Rob Bovey]

I am looping through all sheets in a workbook and come across a sheet
that is named the same a one of the code modules and is
xlsheetveryhidden. I changed the code module name with VBE and re-ran
the code. The name changed to the new name. How could this be
possible?

I believe this is a very old workbook. Could this have anything to do
with macrosheets? The sheet does not have a codename.

This is typical of workbooks with VBA code modules that were created in
Excel 5/95. To convert the module into a current version code module, the
easiest thing to do is to first make sure the module has a valid code module
name (no spaces or non alphanumeric characters). Then export the module,
remove it from the project and import it back into the project. A new
version module will be created upon import and the module name should then
disappear from the Sheets collection.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[deb.gentry]

This is typical of workbooks with VBAcodemodules that were created in
Excel 5/95. To convert themoduleinto a current versioncodemodule, the
easiest thing to do is to first make sure themodulehas a validcodemodule
name (no spaces or non alphanumeric characters). Then export themodule,
remove it from the project and import it back into the project. A new
versionmodulewill be created upon import and themodulename should then
disappear from theSheetscollection.

--
Rob Bovey, Excel MVP
Application Professionalshttp://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Developmenthttp://www.appspro.com/Books/Books.htm

Rob,

Thanks for the solution to fix the code. Now the question is, how can
one test if a member of the sheet colleciton is an old vbacodemodule?
Do you just do a test on type with an on error continue and test the
err.description or is there a better way?

Carl

 

[deb.gentry]

This is typical of workbooks with VBAcodemodules that were created in
Excel 5/95. To convert themoduleinto a current versioncodemodule, the
easiest thing to do is to first make sure themodulehas a validcodemodule
name (no spaces or non alphanumeric characters). Then export themodule,
remove it from the project and import it back into the project. A new
versionmodulewill be created upon import and themodulename should then
disappear from theSheetscollection.

--
Rob Bovey, Excel MVP
Application Professionalshttp://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Developmenthttp://www.appspro.com/Books/Books.htm

Rob,

Thanks for the solution to fix the code. Now the question is, how can
one test if a member of the sheet colleciton is an old vbacodemodule?
Do you just do a test on type with an on error continue and test the
err.description or is there a better way?

Carl

 

[Rob Bovey]

Thanks for the solution to fix the code. Now the question is, how can
one test if a member of the sheet colleciton is an old vbacodemodule?
Do you just do a test on type with an on error continue and test the
err.description or is there a better way?

Hi Carl,

Assuming you're looping the sheets in something like the following
manner, you can use the TypeName function to check for modules. The only
time the type name "Module" will ever appear is if you've got an Excel 5/95
code module in the Sheets collection.

Dim objSheet As Object
For Each objSheet In ActiveWorkbook.Sheets
If TypeName(objSheet) = "Module" Then
MsgBox objSheet.Name & " is a legacy code module."
End If
Next objSheet

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[CG]

Hi Carl,

Assuming you're looping the sheets in something like the following
manner, you can use the TypeName function to check for modules. The only
time the type name "Module" will ever appear is if you've got an Excel 5/95
code module in the Sheets collection.

Dim objSheet As Object
For Each objSheet In ActiveWorkbook.Sheets
If TypeName(objSheet) = "Module" Then
MsgBox objSheet.Name & " is a legacy code module."
End If
Next objSheet

--
Rob Bovey, Excel MVP
Application Professionalshttp://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Developmenthttp://www.appspro.com/Books/Books.htm

Rob,

Thanks a million, just what I needed!

Carl