Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Copy a Value held by a Global Variable into a Cell from within VBA Function

  1. #1
    Join Date
    3rd May 2011
    Posts
    7

    Copy a Value held by a Global Variable into a Cell from within VBA Function

    Hi everyone,

    How can I assign a value held by a global variable into a cell from within a VBA function? Function needs to make some if-then analysis therefore reaching a global variable from within a cell -even if it is possible, is not what I am after. The Function in VBA should be copying the content of a global variable into a cell if certain conditions are meet.

    Is there a way to do so?

    Thanks in advance

    Eros

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    21st April 2011
    Posts
    11

    Re: Copy a Value held by a Global Variable into a Cell from within VBA Function

    If I understand correctly, you have a global variable, for example called AVAR, and you want the contents of AVAR to be put into a cell as a value, say cell A1 in worksheet Sheet1.

    Use the formula:
    VB:
    Option Explicit 
    Public AVAR As String 
     
    Sub putvar() 
        AVAR = "Hello" 
        Worksheets("Sheet1").Range("$A$1").Value = AVAR 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    3rd May 2011
    Posts
    7

    Re: Copy a Value held by a Global Variable into a Cell from within VBA Function

    Hi PJ_in_FL,

    And thank you very much for your prompt return.

    You have used a Sub instead of a Function and this perfectly should work. However, my aim here is different. Even though I know the fact that Functions are not allowed to make any changes to cells as opposed to Subs, my big problem to which I haven't been able to find a reasonable solution is as follows:
    I JUST NEED A FUNCTION TO MAKE CHANGES TO CELLS - sounds like asking for the impossible, I know. Unfortunately, my setup only works with this requirement. Let me explain it to you. I have a huge matrix of table with each cells get live data from a stock feed. Rows hold stock symbols and columns hold detail information for each stock such as Ask, Bid, Volume etc. For each row I call several functions from within several adjacent cells located on the same row. These functions get real-time data from the adjacent cells for the subject stock, control some conditions, and -here is what I fail, need to make changes to some other cells in another worksheet based on results of the if-then calculations just made. I just need to save/record some values held in global variables defined in modules. For example, if the condition in the function is true then I want to hold a bunch of data such as Ask, Bid, Volume in several cells in real-time to record the data that makes the condition true.

    Am I wanting too much? Perhaps Excel is not what I am looking for. This is very very basic requirement for my setup, that is I need functions to make calculations and get the results to different cells to see what output these function calculations just produced. Programs get data, calculate it and produce output. I thought programmability of Excel, -which is where VBA functions come in, would let me do what I intended to do, but seems that I am wrong.

    Any comments would be highly appreciated to get around of this restriction, if there is any, of course.

    Thanks a lot in advance

    Kadir

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    21st April 2011
    Posts
    11

    Re: Copy a Value held by a Global Variable into a Cell from within VBA Function

    Quote Originally Posted by kadirergun View Post
    I JUST NEED A FUNCTION TO MAKE CHANGES TO CELLS - sounds like asking for the impossible, I know. Unfortunately, my setup only works with this requirement.
    Perhaps there is a way around this restriction by going at the problem in a different way.

    Quote Originally Posted by kadirergun View Post
    Let me explain it to you. I have a huge matrix of table with each cells get live data from a stock feed. Rows hold stock symbols and columns hold detail information for each stock such as Ask, Bid, Volume etc. For each row I call several functions from within several adjacent cells located on the same row. These functions get real-time data from the adjacent cells for the subject stock, control some conditions, and -here is what I fail, need to make changes to some other cells in another worksheet based on results of the if-then calculations just made. I just need to save/record some values held in global variables defined in modules. For example, if the condition in the function is true then I want to hold a bunch of data such as Ask, Bid, Volume in several cells in real-time to record the data that makes the condition true.

    Am I wanting too much? Perhaps Excel is not what I am looking for. This is very very basic requirement for my setup, that is I need functions to make calculations and get the results to different cells to see what output these function calculations just produced. Programs get data, calculate it and produce output. I thought programmability of Excel, -which is where VBA functions come in, would let me do what I intended to do, but seems that I am wrong.
    (Emphasis added)
    Can you add Named Ranges to your sheet? If so, perhaps you can use the Named Range instead of the global variable in your cell formula. I tested the following code in the Calculate worksheet event handler and the Named Range "Var1_" did get the value of the global variable AVAR, and updated correctly when AVAR was changed in a procedure elsewhere. I may not fully understand your process you described, but I hope this helps.

    Using the code from the prior post, I added this to the worksheet code:
    VB:
    Private Sub Worksheet_Calculate() 
        Dim xlCalc As Long 
        xlCalc = Application.Calculation ' save and restore present calculation method
        Application.Calculation = xlCalculationManual 
        Range("Var1_") = AVAR ' put the value of the global variable into cell defined by the named range
        Application.Calculation = xlCalc 
    End Sub 
    
    
    However, writing the above and doing further testing, this only works when calculation is set to manual. Perhaps someone more knowledgable in Excel can understand how to prevent stack overflows that occur when calculation is set to Automatic.

    As an alternative, if those global variables can be assigned to the named ranges in your procedure, then the Calculate event code would be unnecessary.

    I hope this help you find an answer.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    3rd May 2011
    Posts
    7

    Re: Copy a Value held by a Global Variable into a Cell from within VBA Function

    Hi PJ_in_FL,

    The problem I am facing here is, my function has to make many if-then checks and based on the results of these checks it has to evaluate, say, 50 some variables and return all of them to the worksheet, as opposed to returning just one value. A single value would have been quite simple to return to calling routine via a function; however, there are 50 some values that I want to keep track of on the fly, and copy/save them into 50 some cells on an Excel worksheet as they change.

    As you see, I just need to use a function, not a sub, to make calculations during run-time, and I also need to record the results on 50 some cells. I simply cannot do this by using a formula which returns only one value; or by using a sub which does not provide on-the-fly calculations. It is kind of a conflict between strengths and weaknesses of functions and subs. I need to use only strengths of both of them, but currently seems to be impossible with Excel.

    Using Worksheet_change() or Worksheet_calculate() events would also not help because I read with DDE links many instantly changing data from a server feed and it would give enormous calculation workload to Excel. I send these always-changing figures from cells to functions and expect my functions make calculations and assign the respective results to many different cells so that I could dynamically see the most recent calculation results.

    I hope this clarifies my concerns

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    2nd May 2008
    Location
    Ubique
    Posts
    1,886

    Re: Copy a Value held by a Global Variable into a Cell from within VBA Function

    Cross-posted here.

    Please note that if you cross post in multiple forums you must post links to the other posts in each of your posts.
    Rory
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Global Variable For UserForm
    By ra00 in forum EXCEL HELP
    Replies: 6
    Last Post: January 2nd, 2008, 21:09
  2. Global Variable Declaration
    By Kjartan in forum EXCEL HELP
    Replies: 3
    Last Post: December 26th, 2007, 18:52
  3. Assigning A Global Variable
    By muabao in forum EXCEL HELP
    Replies: 2
    Last Post: February 9th, 2007, 04:03
  4. Declaring A Global Variable
    By cgi_pro in forum EXCEL HELP
    Replies: 4
    Last Post: October 21st, 2006, 16:48
  5. Global Variable
    By tgowrineni in forum EXCEL HELP
    Replies: 4
    Last Post: April 28th, 2005, 17:21

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
  •  
porno