Copy Global Variable into a Cell in VBA Function

[eros]

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

 

[Ruddles]

You may not modify the contents of a worksheet cell from within a function, or from a subroutine called from a function.

How are you calling the function and are you returning anything from it?

My suggestion would be to return the value to the calling routine and copy the value to the worksheet in the calling routine.

If you can't work this out, post the function code and the line which calls it.

 

[eros]

Hi Ruddles,

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 be 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.

I hope this clarifies my concerns

 

[pgc01]

Hi

Can you post a working example, dealing with 4-5 values instead of 50?

This will help us understanding the problem and what the possible approaches may be.

Post inputs, logic and expected results.

... I simply cannot do this by using a formula which returns only one value; ...

Not true. A formula can return more values.

... by using a sub which does not provide on-the-fly calculations. ...

Not always true. You can ususally use an Event Sub.

 

[Ruddles]

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.

Two possible approaches:-

• If you pass the variables fto the function by reference (ByRef), changing them inside the function will also change their value in the calling routine. You can then move them to the worksheet in the calling routine.
• Return the variables to the calling routine in an array.

But as PGC says, seeing the code might help.

 

[eros]

Dear all,

Thanks a lot for your promt return. That means a lot to me.

Using Worksheet_change() or Worksheet_calculate() events would not help because I import live data with DDE links from an external feed changing many times a second. Both data that I use within functions and output data that I want to see as a result of calculations inside functions are all on the very same Excell sheet therefore I presume it would cause enormous calculation workload.

Moreover, I send live data to functions as parameters and expect my functions make calculations and create new data and assign them to new variables, that is to say, not use the very same data which was received as parameters, assign the respective results to many different cells so that I could dynamically see the most recent calculation results on the fly.
Therefore, it does not seem to make sense to me trying to send data by reference or using arrays. The calculation results need to be written back on the Excel sheet on different cells, not into the cells which actually hold the live input data.

I will try to figure out a simple example even though I don't think it would add benefit as in any case I won't be able to demonstrate the live data links etc.

Meanwhile, if you come up with other suggestions please feel free.

many thanks and regards,

 

[eros]

Dear all,

First off, I understand according to the forum rules I have to advise that I crossposted here: http://www.ozgrid.com/forum/showthread.php?t=153807&goto=newpost. I will omit the other thread and continue from here.

Here is my sheet layout and contents of the cells as promised.
A1=DDELinkDataA1 B1=DDELinkDataB1 C1=DDELinkDataC1 and 17 more columns follow
A2=DDELinkDataA2 B2=DDELinkDataB2 C2=DDELinkDataC2 and 17 more
columns follow
and 48 more rows
Eventually it is a 50rowx20column matrix of cells which are updated several times a second from a feed

On the same sheet there are following functions which get live data from some of the cells above.

U1=Function1(A1,B1,C1, etc as necessary) V1=Function2(A1,B1,C1, etc) and 8 more functions
U2=Function1(A2,B2,C2, etc as necessary) V2=Function2(A2,B2,C2, etc) and 8 more functions
... and 48 more rows
This is a 50rowx10 column matrix of functions. There is a separate function for each column and these functions get data from the same row as parameters. These functions make a lot of calculations using the cell values from the first DDE matrix above and create new values for each row about which I need to keep track of and see on the fly by putting them into other cells on the very same Excel sheet such as:

AF1=ResultAF1 AG1=ResultAG1 AH1=ResultAH1 and 6 more columns
AF2=ResultAF2 AG2=ResultAG2 AH2=ResultAH2 and 6 more columns
and 48 more rows
This becomes a 50rowx9column of results matrix.

Here are my problems and in fact comments to your suggestions:
1-Event subs won't work out as there are many cells changing instantly and that would cause huge calculation traffic
2-ByRef call won't work out because actually none of these functions need to return a value to the cell they are called from. Actually, all functions are expected to make calculations, create some values and put them into the cells that form the Results matrix.

Puff! I hope I managed to make it clear. I know what I want is not something common and seems against how functions/subs/events work. I am just trying to use excel sheet to feed my functions and use the values calculated by functions for debugging on the fly, all on the very same Excel sheet.

Any different approach as to how to manage it?

Many thanks and regards,