+ Reply to Thread
Results 1 to 2 of 2

Accessing a global array variable from within a cell

  1. #1
    Registered User
    Join Date
    07-16-2019
    Location
    Newcastle NSW
    MS-Off Ver
    Office 2019
    Posts
    1

    Accessing a global array variable from within a cell

    Hi,
    I am new to the forum and hoping that you guys can shed some light, I have been searching forums for a few days with no luck. I need to create an array within VBA to store calculated values

    [Option Explicit]
    [Public calc_array (1 to 450, 1 to 9) as Double]

    Would be the right syntax. Each row of the array will hold 9 values and be calculated via a call to a function which is complicated but still ultimately calculates 9 rational numbers, current size of the spreadsheet is only 450 rows and this will grow by one row per month, something like

    [Function fill_calc_array ( rowvar as Integer, var_1 as Double, var_2 as Double, var_3 as Double,........) as String]

    [ calc_array(rowvar, 1) = var_1 * var_2 / var_3]
    [ calc_array(rowvar, 1) = var_2 * var_3 / var_1]
    [ calc_array(rowvar, 1) = var_3 * var_1 / var_2]

    [ fill_calc_array = "Complete"]

    [End Function]

    I want to be able to access the calc_array from within the VBA function but also be able to access the calc_array from within a specific cell, again like the following is what I would like to use:

    Cell J5 is the month value between 1 and 450 , in cell K5 "=calc_array(j5,1)", L5 = "=calc_array(j5,2)", M5 = "=calc_array(j5,3)" etc

    Try as I might I cant reference the global array variable from within a cell, so I wrote a function to access the array

    [Function access_global_array(rowvar as Integer, colvar as Integer) as Double]

    [ access_global_array = calc_array(rowvar, colvar)]

    [End Function]

    But this made the spreadsheet very slow (15 seconds to recalculate on a very fast machine).

    Any ideas? Thanks Gil64
    Last edited by gil64; 07-17-2019 at 05:19 PM. Reason: Adding Code Tags

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Accessing a global array variable from within a cell

    Welcome to the board. Two things:

    o Post a workbook so we can see the full code (a few lines of desensitized data is adequate) that shows how the way you would prefer to do it doesn't work.

    o Take a few minutes to read the forum rules, and then edit your post to add CODE tags.
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Using the value stored in a Global Variable in a cell's formula
    By lovecolorado in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 03-25-2019, 11:26 PM
  2. Using a Global Variable in a formula, in a cell, in a Worksheet
    By lovecolorado in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-19-2019, 08:33 AM
  3. Replies: 5
    Last Post: 06-18-2015, 09:10 AM
  4. [SOLVED] Accessing Global Variables in an Excel formula
    By briggy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-21-2012, 05:09 AM
  5. [SOLVED] Global variable declared in Userform. Variable value is not sticking
    By timtim89 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-25-2012, 08:50 AM
  6. Accessing Global Variables defined in ThisWorkbook Module
    By smz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-02-2010, 08:26 PM
  7. keep recursion result (a dynamic array) without using global variable
    By lvcha.gouqizi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-25-2005, 03:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1