473,320 Members | 2,107 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,320 developers and data experts.

Excel Module (Code) Names

NeoPa
32,556 Expert Mod 16PB
Introduction :

Excel worksheets all have names, but although the default name for a sheet will always match the module name for that sheet, it is easy to change the name of the sheet, yet not so easy to access and change the module name. If this is a problem you've struggled to deal with, then read on.


Worksheet Name :

This is typically shown in a tab at the bottom of a workbook window in Excel. Double-clicking in that tab allows the operator to overtype this with a new name. This is also visible and changeable by a developer if they look at the Properties Pane (See Debugging in VBA.) in the VBA IDE window. You'll notice there are two properties named 'Name', although one is in parentheses '(Name)'. We'll come to that later. Only the one without parentheses refers to the worksheet name itself. The Worksheet object (when used within VBA) also has a .Name property which reflects this name. This property is updatable in code.


Worksheet Module Name :

The module name of the worksheet is not visible anywhere in the main Excel window, but can be seen (and modified) in the VBA IDE via the properties. The '(Name)' property is actually the name of the worksheet's module and is R/W. It can also be seen via VBA as the .CodeName property of a worksheet object. This is only R/O however.


How Do I Update the Worksheet Module Name in VBA then :

Another way to access this code module is via the VBComponents collection of a VBProject, which can be found as a property of a Workbook. This is easiest if done with the VBA Extensibility library referenced. The version I have for 2003 is named "Microsoft Visual Basic for Applications Extensibility 5.3" (To help readers find their own version). A VBComponent object has a .Name property which is R/W.

In short then, the basic code to update the module name for a worksheet (The scenario is that we've already named "Sheet1" as "Bob" and we would like the module name to match it.) is as follows :
Expand|Select|Wrap|Line Numbers
  1. Call RenameModule("Bob")
  2.  
  3. Private Sub RenameModule(strNewName As String)
  4.     Dim strSheet As String
  5.  
  6.     With ThisWorkbook
  7.         strSheet = .Worksheets(strNewName).CodeName
  8.         .VBProject.VBComponents(strSheet).Name = strNewName
  9.     End With
  10. End Sub

Conclusion :

Although Excel seems to restrict the renaming of its Module objects via VBA code, it will allow it for those who are prepared to go beyond the very basics. Maybe the designers felt that developers who merely have that very basic understanding of the object structure are better steered clear of such changes anyway. Hopefully though, if you've found this, and have a real need to do so, then you now have the understanding to handle it.
Feb 24 '12 #1
0 4866

Sign in to post your reply or Sign up for a free account.

Similar topics

10
by: sam | last post by:
Hi group, I m wondering which Excel module is good to be used by Python? Thanks Sam
0
by: Query Unknown Excel Worksheet Names | last post by:
I have been trying to write a dynamic application that would allow a user to import data from any Excel spreadsheet and populate that data into a Datagrid in .net. I am successful when i hardcode...
4
by: weboweb | last post by:
Hello group, I hope I've come to the right place to post my question! I am trying to get the worksheet names from an excel file using ODBC, but for some reason I get SQL_NO_DATA error code...
0
by: Bob Palank | last post by:
In creating content for a VC++.NET programming class, I'd like the students to create a single form application starting with an Empty Project(.NET) and then construct the application step by step...
1
by: Nikolay Petrov | last post by:
I have to read data from MS Excel files and I use OLEDB reader for that. The problem I encounter is that I don't preliminary know the names of the sheets in the Excel files, so I can include them...
3
by: Mark | last post by:
Not sure if this is the proper forum, but we'll give it a try. I want my VB.Net program to open an Excel workbook and then execute a VB procedure residing in the Excel workbook. I'd like to...
3
by: David Bear | last post by:
I'm trying to use os.chmod and am refered to the stat module. Is there are explanation of: * S_ISUID * S_ISGID * S_ENFMT * S_ISVTX * S_IREAD * S_IWRITE * S_IEXEC
0
by: madhubabumallidi | last post by:
i am designing a web page so that i need to browse a folder and capture all excel file names in that folder, using c#
2
by: KC-Mass | last post by:
Is it possible to read the spreadsheet names in an Excel workbook with VBA and no Excel?
2
by: Ehsan arman | last post by:
I was wondering how I could read excel file names in a particular folder using Access VBA. I need to compare the file names in that folder with what I have in a field of Access table.
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.