• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 542
  • Last Modified:

Can I use DIM as Worksheet then reference the DIM in a array formula in macro?

I wish to reduce the number of characters in a formula that is placed in a cell from a macro. My problem is the formula uses too many characters and by reducing the number of characters in the formula all works great.
Can I used "DIM As Worksheet" then reference the "New Name" in the formula for all to work. The current longer names of the worksheets need to remain for other reporting processes.
I have attached a simulation workbook for testing please use as required.

Any assistence with this problem is greatly appreciated.

THANKS
WS-Dim-Code.xlsm
0
user2073
Asked:
user2073
  • 4
  • 2
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
The token Dim is a reserved word in VB and so cannot be used as a variable name.

But you can name a tab "Dim" or "DIM" and then reference it in VBA like so:

    Dim TargetWorksheet As Worksheet
    Set TargetWorksheet =ThisWorkbook.Sheets("Dim")

You can use it in formulas:

    Cell.Formula = "=Dim!A1"

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Or are you asking if you can refer to an alias tab name in a worksheet formula?

You can - so to speak - but not at the worksheet level. You can name the ranges of cell you want to reference in your formulas and then use the name instead of the range reference.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Named ranges are a convenient way to reference a single cell or range of cells. To name a range, select the range to be named and enter the name in the Name Box located on the far left of the formula toolbar. The range name can then be used in formulas and VBA code to reference the named range. To reference in a formula use the name anywhere a cell reference would be used:

   =SUM(MyNamedRange)

To use a named range in VBA replace the cell reference text with the range name:

   Set MyRange = Range("MyNamedRange")

To use the same name on two or more different worksheets, choose the menu command Insert->Name->Define. In the Names text entry box enter the worksheet tab name followed by an exclamation mark followed by the range name. Note that the worksheet on which the range being define is located must be active and the range reference in the Refers to text entry box must be on the same worksheet or the name can't be added. Also, when editing named ranges created this way the worksheet on which the ranges are defined must be active to see them in the Define Name dialog box. Named ranges created this way are sheet level named ranges.

To assign the same name to more than one worksheet with VBA code use the Worksheet.Names.Add method as illustrated below.

   Sheets("Sheet1").Names.Add "MyNamedRange", Sheets("Sheet1").[A1]
   Sheets("Sheet2").Names.Add "MyNamedRange", Sheets("Sheet2").[A1]

To reference a name used on more than worksheet use the worksheet object as a qualifier as illustrated below.

   Set MyRange = Sheets("Sheet1").Range("MyNamedRange")

To name cells across multiple contiguous worksheets (a 3-D reference) choose the menu command Insert->Names->Define, enter the range name in the Names text entry box. In the Refers to text entry box clear any existing text and enter an equal sign. Enter the worksheet qualifier as FirstTabName:LastTabName followed by an exclamation mark followed by the cell reference. If either tab name contains a space then enclose the worksheet qualifier in single quotes as illustrated below.

   'Sheet Tab 1:Sheet Tab 2'!A1

Kevin
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
user2073Author Commented:
I have tried to use:
        Dim sh1 As Worksheet
        Dim sh2 As Worksheet
        Set sh1 = ThisWorkbook.Sheets("SummaryOfEmployee")
        Set sh2 = ThisWorkbook.Sheets("EmployeeData")

        ActiveCell.FormulaR1C1 = "=SUM('sh1'!R2C2+'sh2'!R2C2)"

The return is #REF

I need to reduce the number of characters is the actual working file.

THANK YOU
0
 
zorvek (Kevin Jones)ConsultantCommented:
You can't do it that way. The Excel worksheet doesn't know anything about variables defined in the VBA project.

You have to name the ranges and then use the range name in the formula. See my third post above.

Kevin
0
 
user2073Author Commented:
I was hoping for a possible solution by using the process that I have shown. I have tried the range name method as you descripbed in item 3, it works fine. I'll have to change my thinking and use many range names within the worksheets.

I appreciate your assistence.

THANKS
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now