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

Posted on 2012-09-05
Last Modified: 2012-09-05
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.

Question by:user2073
    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    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"

    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    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.

    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    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:


    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


    Author Comment

    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.

    LVL 81

    Accepted Solution

    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.


    Author Closing Comment

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now