writing a macro in Visual Basic; want to assign a variable to a Worksheet name, using a defined Excel name from my workbook; is this possible? (have tried numerous ways to do this and none have worked)
Example:
Worksheet (variable value). Select
where variable name is a defined name in my Excel workbook, as opposed to a static worksheet name in the model
Worksheet ('Project 1'). Select (the named reference is a variable value Project #, where # is variable
Yes, it's possible but it's really not worth the effort. And, here's why together with a better way.
The only reason to not hardcode it in your code is so that it can be changed without touching the VBA project. That's a good goal. But, if it is a named constant, then to point it to another sheet someone must still change the named constant. And, frankly, not everyone is comfortable messing around with names.
So, create a control worksheet (name it configuration or control or parameters or whatever terminology makes your consumer comfortable) and put the information in a cell in that sheet. Now, name this cell. Let's say the name is SheetToUse. Now, use this name in your code as in
If you insist on using a named constant, I'd modify Rick's approach slightly to allow for the presence of a double quote in a worksheet name. Use
Windows 10 to 7 after 30 days. Some people have issues upgrading, others have been reporting compatibility issues, the Windows Store not updating apps (which has already been fixed), and a.
The replace function caters to the fact that the name.value returns two double quotes where there should be only 1.
Tushar Mehta (Technology and Operations Consulting) www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials) Microsoft MVP Excel 2000-Present
I am having an issue with an excel macro. In Excel 2003 we have the Essbase addon which converts 0's to text format if there is not data to pull. We need these 0's to be in number format and I am trying to update the macro to do just that. Unfortunately it seems as though VB does not convert from text to number. The code I am using is below. Please help: ' SET SHEET OPTIONS FOR DISPLAY X = EssVSetSheetOption(WorkBookName & TabName, 5, 1) ' Set Indent setting to No indentation X = EssVSetSheetOption(WorkBookName & TabName, 12, False) ' Set to Turn OFF adjust columns X = EssVSetSheetOption(WorkBookName & TabName, 9, '0') ' Set #missing label to string 0 ' SET SHEET OPTIONS - MODEs X = EssVSetSheetOption(WorkBookName & TabName, 16, True) ' Set Mode Retrieval to Free Form - so to set suppression X = EssVSetSheetOption(WorkBookName & TabName, 11, False) ' Set Preserve Formulas On X = EssVSetSheetOption(WorkBookName & TabName, 6, False) ' Set to Turn OFF #missing suppression X = EssVSetSheetOption(WorkBookName & TabName, 7, False) ' Set to Turn OFF zero rows X = EssVSetSheetOption(WorkBookName & TabName, 16, False) ' Set Mode Retrieval to Advanced Interpretation X = EssVSetSheetOption(WorkBookName & TabName, 19, '0') ' Set #no access label to string 0 X = EssVSetSheetOption(WorkBookName & TabName, 6, False) ' Set to Turn OFF #missing suppression X = EssVSetSheetOption(WorkBookName & TabName, 7, False) ' Set to Turn OFF zero rows X = EssVSetSheetOption(WorkBookName & TabName, 11, True) ' Set Preserve Formulas On
X = EssVRetrieve(WorkBookName & TabName, Range(RngName), 1)