Most feasible way to work with Worksheet names in VBA code

-

1.       When you look in your VBA project screen it will represent a worksheet name  “Sheet1” (which is the default name when you open a workbook) as follows in the VBA project explorer:
Microsoft Excel Objects
Sheet1(Sheet1)                ßThe name in brackets is the name as you see it in the workbook and is the worksheet name.
                                               ß The name on the left is the code name.
The worksheet name can be changed in the workbook or in the code module by selecting the sheet object and then changing the Name property in the properties window.
The code name can only be changed in the properties window under the (Name) property.
2.       To refer to the worksheet name in code we refer to it in the code module as follows:
ThisWorkbook.Worksheets(“Sheet1”).Range(”A1”) = 66
3.       To refer to the worksheet by using its code name you can use the following code and it can be referred to directly.
Sheet1.Range(“A1”) = 66
4.       The benefit of using the code name for  the worksheet instead of the worksheet name is that when the user change the name of the worksheet the code will still work. This is because the code refers to the code name for the worksheet and not to the worksheet name.
To use the code name of the worksheet the worksheet must be in the same workbook than the code.

Comments

Post a Comment

Popular posts from this blog

VBA Developer - Digitally sign your macro project

-
Image
Digitally sign your macro project This article describes how you can digitally sign a macro project by using a certificate. If you don't already have a digital certificate, you must obtain one. To test macro projects on your own computer, you can create your own self-signing certificate by using the Selfcert.exe tool. Obtain a digital certificate for signing You can obtain a digital certificate from a commercial certificate authority (CA) or from your internal security administrator or information technology (IT) professional. To learn more about certificate authorities that offer services for Microsoft products, see the list of  Microsoft root certificate program members . Your macro projects and Microsoft Office Because a digital certificate that you create isn't issued by a formal trusted certificate authority, macro projects that are signed by using such a certificate are known as  self-signed projects . Microsoft Office trusts a self-s...
Read more

Distribute your VBA project to other Microsoft Users

-
We often spend a lot of time to write VBA code to assist us in completing tasks in Microsoft Applications whether an Excel spreadsheet, Access database, Outlook Message, Word Document. This code can be used by other users on the same scenario. We can make our code available to other users. This code can be protected to ensure integrity. The procedure below describe how to create distributed software for an Excel application. The process for other Microsoft applications will be similar. 1. Once you have completed, debugged and tested your program and are happy that it works, protect      your code as follows: Right click on your module or project name and select VBAProject Properties. Assume your sub name is "ProtectedCode" In the General Tab type in the name of your project e.g. "CodeForDistribution". In the Protection Tab select Lock project for viewing  and enter a password for your project. Click on OK. 2. Save your project but select the file type...
Read more

How far can you stretch the Excel LET function?

-
Image
 Excel LET Function Definition and background and Syntax The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax. To use the LET function in Excel, you define pairs of names and associated values, and a calculation that uses them all. You must define at least one name/value pair (a variable), and LET supports up to 126. =LET(name1_of_variable1, name_value1, calculation_or_name2_of_variable2,[name_value2_of_variable2, calculation_or_name3_of_variable3  . . .  ]) The LET function can take up to 132 variables. The LET function with more than one variable can look like this: =LET(name of variable1, name_value1, name of variable2, name_value2, calculation) Argument Description name1 Required The first name to assign. Must start wi...
Read more