EVALUATE an R1C1 formula currently stored in an array

I've constructed an array from a large data set. All through the data are blank cells and I have a formula I am putting into the empty cells to provide the missing values.

In memory, I fill the empty array with the R1C1 formula, when the array is written back out to the sheet, the formulas automatically calculate, all is well. ANd I an spot these changes by simply searching for cells with formulas in them.

All good.

I now have a need in the macro running process to actually EVALUATE the formula I have stored in memory. For example this text string is stored in the array waiting tom be written out:

"=IFERROR(INT(R36C/R36C[+1]*RC[+1]), 0))"

How can I EVALUATE() that in-memory R1C1 formula to get the results of the calculation even though it is not in a cell?

The evaluate of that formula depends on what cell you are planning on putting it in.
Try something like

Please Login or Register  to view this content.

Originally Posted by mikerickson

The evaluate of that formula depends on what cell you are planning on putting it in.
Try something like

Please Login or Register  to view this content.

Reading this on my phone, not at my computer until later to check this out. But reading it it appears the solution might be using a cell on the workbook. I definitely don't want to do that. I have already constructed this r1c1 formula in memory and will write it out later to the cell. But in the meantime I'd like to know what the result of the calculation is, because I'm trying to solve another problem. Since the data set is monstrously large, I can't stop and reference cells on the worksheet or it'll just kill the whole process.

It only uses a cell as a reference; there's no other way to find out what the relative references refer to.

Please Login or Register  to view this content.

Hello Jerry
I could have missed the point totally about what you want . If so , – never mind – it was about time I got to grips with the different RC Number Formats,... which I did by writing a code .... a code of a sort I wanted for some time to do anyway..
_..

The assumptions I have made about what you wanted are...
_ you start with a Range that has values ( or “Empty cells” ).
_ you make your Array from that ( probably in a one liner Array()= Range.Value thingy ) ....and_...
_... you are talking about a range of a single rectangular Area of contiguous Cells,
( my rngJerry for demo is just A1:D3 )

Further I assume..
_ You have the possibility to get an Array() identical in size to your Constructed Array() but having all the values in it that will be needed for any formulas that you may make. ( I guess that must be the case – it will just be your Array() before you add bits, - or after come to think of it, - you can just use the same Array() I guess. I used two different ones.

_ For a simple demo of the idea, I assume your Range and Array start at “co ordinates” 1 , 1. ( But if that was not the case, then you just need to add a couple offset constants in the code – so that would be no problem.) What I mean is any Array(1, 1) corresponds in my demo to Range(“A1”) etc._.....
_....As you say “ I fill the empty array with the R1C1 formula, when the array is written back out to the sheet, the formulas automatically calculate, all is well „ then I think my assumptions should be OK


Very Brief Code Description first
Rem 1)
I set up a demo Range with some formulas in it. ( I know that you put your formulas in the Array , but I am just making a demo that can be run in its own.. )
That Range goes into two identical size Arrays(). One has just values:
'1d) a prerequisite for demo to put all values of your Range in a Values held in Variant Type Element Member Array(), arrJerryValues()

Rem 2). The other Array is the one you work on,
arrJerryCntst() = rngJerry.FormulaR1C1


Rem 3)
I go through all cells in the Array, when I hit a formula start doing stuff. The idea is simple. I assume the relative position of formulas and values in the Array()s are consistent with the worksheet ( again that must be true, or this “ I fill the empty array with the R1C1 formula, when the array is written back out to the sheet, the formulas automatically calculate, all is well “ would not be correct
( As noted , if you are not starting at the origin of a Worksheet, with your Array()s then you just need some offsets added in the code to allow for that )
I change a thing like =R[1]C[1] to an absolute thingy, like if that was your simple formula in the first cell then in order to evaluate it I
_ first change it to
=R2C2
then
Rem 4)
I replace the RC reference bit to the actual value from the corresponding Array() value
then
what Evaluate uses is the formula with all the values it needs as values in it
So like if in Cell ( 2, 2 ) , Or rather Array( 2, 2, ) , if I had a value of 2 in B2 then I am actually evaluating finally in my code
= 2

_.............................
_.___________________

So the code starts and puts some arbitrary values and formulas in a spreadsheet ( The values would be your data, and the formulas represent those you have placed into your Array - I have just put the formulay first into the spreadsheet to demo ) The following is what Rem 1) in my code is about

Using Excel 2007 32 bit

Row\Col	A	B	C	D
1	=R[1]C[1]
2	2	2		=R2C1+R2C2
3				=R[-1]C[-3]+R[-1]C[-2]

Worksheet: RCFormulas

_..
Or, if you like, put another way, at some point when you have not quite finished yet, your Array looks like this: ( So you have at the following point not put formulas in all the Blank Array Member Element yet ) The following is at Rem 2) in my code where you have your Array() , arrJerryCntst()

=R[1]C[1]
2	2		=R2C1+R2C2
			=R[-1]C[-3]+R[-1]C[-2]

_.......

These would be the results after you paste out to the Worksheet

Using Excel 2007 32 bit

Row\Col	A	B	C	D
1	2
2	2	2		4
3				4

Worksheet: RCFormulas

But my Code does not do that. ( It does not paste to or referrence any spreadsheet
What it does is loops through and in this case 3 Message box pop up giving you full details of all the formulas and what they Evaluate to

RCJerry1.jpg
http://imgur.com/3hg9xh2
Attachment 488085
RCJerry2.jpg
http://imgur.com/xAJHP4O
Attachment 488086
RCJerry3.jpg
http://imgur.com/hF4QVwe
Attachment 488087

I won’t do a full code explanation just yet ( It’s all in the ‘Comments ). I expect I have missed something out and it will almost certainly muck up for more complicated formulas, so I will probably chop and change it sometime in the future and post back..

Here’s the first version:
http://www.excelforum.com/showthread...t=#post4517161
http://www.excelforum.com/showthread...t=#post4517163



Alan

Thanks mikerickson that works perfectly. Just what was needed.

Thanks all for the added info, something new for me.

Hello Jerry
Just out of interest, when you have time, could you show with a simple example how you are using the mikerickson or shg codes to do whatever it is you are wanting to do.
I think I must have missed the point
Thanks
Alan

For example For my Formula Range, and considering the Formula I have in D3_...
Using Excel 2007 32 bit

Row\Col	A	B	C	D
1	=R[1]C[1]
2	2	2		=R2C1+R2C2
3				=R[-1]C[-3]+R[-1]C[-2]

Worksheet: RCFormulas
_....
This would be the use of the mikerickson or shg codes to get the correct answer.

Please Login or Register  to view this content.

But they only work on the Worksheet, which I thought you did not want to do
Effectively with Evaluate( ) you are pasting that formula into ( D3 ) in my case, and then copying that back to the variable EvalR1C1.** They only work if you have the Worksheet and access it.

Originally Posted by JBeaucaire

But reading it it appears the solution might be using a cell on the workbook. I definitely don't want to do that....

** Albeit, you are not pasting into and copying from your actual Worksheet, but using some copy of it. But you are accessing the Worksheet, I think..

Originally Posted by JBeaucaire

.... Since the data set is monstrously large, I can't stop and reference cells on the worksheet or it'll just kill the whole process.

_.. I think with the mikerickson or shg codes you are accessing the worksheet – in my example you are accessing A2 and B2
I am Just asking out of interest, so just reply if and when you have the time


Alan

I'd use:

Please Login or Register  to view this content.

Hi snb
I have no idea what that code should do. It errors by me – as expected – at some point it will try to divide by zero
I guess the clue is 36 – 36 is in Jerry’s formula, but looks like I have totally missed the point again
Alan

Please Login or Register  to view this content.

1) Picture a data set that is 100s of thousands of rows long, 100s of columns deep. The columns represent months of medians in real estate values from all over the place.

2) Picture holes in the data, starting in column 10 (the first dated column) and continuing indefinitely across that row until the data finally starts for that row.

3) My client had a need that we fill in those blanks with a formula. To do this, we first had a routine that did a very lengthy check on all the rows that are NOT empty in that first column where this row with blanks had its first value...so we're checking the entire database looking for the best "match". Once we determine which row we are going reference for this particular row of empty cells, we construct the formula shown. In the example I gave originally, the row that had been found was row 36.

4) Since we are updating 100s of thousands of empty cells, putting the constructed formulas into the worksheet cells was too slow. So, since we had the entire set of data in an array where we were doing all this work, I started putting the formulas into the array as well.

5) The end result we wrote back out to the sheet. All the cells with formulas in them are highlighted to make it easy for the user to review the results, row by row.

6) Since the formulas did not exist on the worksheet as the macro was writing them into the empty array fields, their "results" did not exist yet, not in my mind. So I had been excluding those rows from future matches as we continued to work.

A new requirement added at the end of this project required that I allow the previously empty cells to be a possible match for further empty rows were then working on. Thus my question here, as I was doing the "check against all other filled rows" for a new empty row the macro was working on, I now needed to be able to detect the future results of those formulas I had been creating as I went.


Anyway, it does the trick.

code illustrated.

I would avoid Excelformulae in VBA.

@Jerry
Hi Jerry
Thanks very much for coming back here. Not sure I quite grasp exactly what you are saying. ( ** Edit – or maybe I have ) I am not sure if there is a particular significance to the formula – I thought it was just an example. But never mind, the penny may drop I expect now that I have that extra info from you. ( ** Edit – and from snb’s post )
Possibly naively I just went by the Thread title.. I myself have a massive Worksheet where there are some empty cells and it would be handy to have a Formula in the captured Array that , say does an average for the missing values based on all the others in the column, for certain calculations, but does not affect the Worksheet should values missing be added later....
_....Thus my answer

Anyways...
So I will just wrap up my l Additional solution to “EVALUATE an R1C1 formula currently stored in an array '_=...

I tidied up the long code I did which looped through all Array formulas and evaluated them . I added a bit so that a Text in a formula that has something like RC would not be mistaken and did a Functions versions that just evaluates a single formula taking similar parameters as the shg and mikerickson Functions. It takes also in your Values Array. Then it evaluates the given Formula taking the values from the Array, not the Worksheet.

If nothing else I have a clear picture in my head now on what all the R C Stylio Formula Format is about. - - I noticed for example a couple of important points to do with the two ### types that no Blog has mentioned and can ( at least did me ) throw one off a bit : _

(### There are Absolute and Relative types
_ – like =R3C2 is Absolute and written anywhere means Cell(3, 2) . Conventionally if you paste such a formula in any Cell it comes out as =$B$3.
_ - like if written to the first Cell, this =R[2]C[1] means 1 “across”, 2 “down” from the so is also referring to Cell(3, 2) . This is Relative type. Conventionally if you paste such a formula in any Cell it comes out as =B3.
R1C1 notation
You can also mix the two types up ! )
_1) The expression R1C1 is used generally to refer to the R C type convention or “Style”, both the Absolute and Relative versions.
R1C1 in a formula usage is actually an Absolute version. So that can confuse.
The .FormulaR1C1 Property, for example can be used, for example, to assign a Relative R C type such as R[1]C[1] and also an Absolute type such as R1C2, ( or mixed )

_2) It is good to note that something like R[1]C is a “shorthand” for Relative R[1]C[0]. Both give the same result. Obvious with hindsight but I found it nowhere written.

_......

So I broke it down into 2 Functions. The first converts the given R C Stylio Formula to Absolute R C style Format from Relative R C style Format or mixture from both Formats.

Function RelToAbsFukOffSqBrK( ) is here:
http://www.excelforum.com/showthread...=1#post4517613
http://www.excelforum.com/showthread...=1#post4517621

Function EvalRCformulaCurrentlyinArray( ) is here:
http://www.excelforum.com/showthread...17#post4517717
The second does the evaluation of the formula using the Array values. In addition the final Optional argument allows you to specify what the Top left Range was if it was not A1 when you originally captured your Array from the Worksheet



I tested it with this Range, ( that is to say I tried each String Formula , in the Functions with the appropriate values at the Call lines )
Using Excel 2007 32 bit

Row\Col	A	B	C	D
1	=R[1]C[1]	=R[1]C	="TextRC """
2	2	2		=R2C1+R2C2
3				=R[-1]C[-3]+R[-1]C[-2]

Worksheet: RCFormulas


Here is a full demo code:
http://www.excelforum.com/showthread...17#post4517717

Here just a simplified demo just for the Formula in D3

Please Login or Register  to view this content.

In the above demo code, the Function RelToAbsFukOffSqBrK( ) returns
“=R2C1+R2C2”

The Function EvalRCformulaCurrentlyinArray( ) first gives a message box
RCJerry4.jpc
http://imgur.com/SoFdngo
Attachment 488224

then returns
4




Alan

P.s. A variation of this could be to simplify the First Function to use the

Please Login or Register  to view this content.

to return the xlA1 type style and modify the second function to use that.
May be something to do, somewhere.. sometime..
_.....................................



EDIT: I think maybe I now have it all mostly sussed ** - see next post

Originally Posted by snb

code illustrated.
I would avoid Excelformulae in VBA.

Hi snb
I think with your File I may have finally twigged, ..... at least enough


First I had to reduce the data a bit, ...
Using Excel 2007 32 bit

Row\Col	A	B	C
1	212	230	248
2	302	320	338
3	392		428
4	482	500	518
5	572	590	608
6	662	680	698

Worksheet: snbSheet1

_... and slightly modify your code:

Please Login or Register  to view this content.

Then with Jerry’s explanation and your results I got it that the idea of the ( example) formula from Jerry was to estimate a value for that empty cell. ( Not far off something I actually may need )
The exact formula and reasoning is unimportant for me. But now I could move on, and after a couple of hours I saw Jerry’s deliberate typo too ! ( the extra bracket at the end ) and corrected that as well as modifying it to suit the reduced range. So Finally I have

"=IFERROR(INT(R5C/R5C[+1]*RC[+1]), 0)"

If I were to paste that formula into B3 then I get in B3 415, just as i do with your demo output offsetted output:

212	230	248
302	320	338
392	415	428
482	500	518
572	590	608
662	680	698

_.....

But Jerry don’t want to paste that formula yet , but he wants to get that 415 result
So now a last demo for “ EVALUATE an R1C1 formula currently stored in an array ... “ ,
that is to say_....
“....Evaluate "=IFERROR(INT(R5C/R5C[+1]*RC[+1]), 0)" which is currently in an Array of values which was got from a Worksheet where that the Formula is intended to go, but where I don’t want to paste it in just yet, - I would rather like to evaluate it “internally” using the filled values Array....”
_...
So my final demo code:

Please Login or Register  to view this content.

My first Function RelToAbsFukOffSqBrK( ) returns the adjusted to Absolute Type R C Stylio Formula
=IFERROR(INT(R5C2/R5C3*R3C3), 0)
My second Function EvalRCformulaCurrentlyinArray( ) first gives a message box :
RCJerry5.JPG
http://imgur.com/NKdcNCZ
Attachment 488225


then it returns the value
415

It makes no reference to the Worksheet ( other than using any Range reference as a convenient way to get .Row and .Column co ordinates – but that could also easily be done with a formulas ).
The formula is evaluated with no reference to any Worksheet Ranges
Bingo !

Thanks snb, ( and Jerry ) , I have it pretty well sussed now

Alan

P.s. I too dislike Formulas in my own work. I wasted ages with loads of complicated ones before I learnt VBA. Based on my experience I would learn VBA right at the start of Excel if I had my time again.
But strangely I actually enjoy sometimes answering questions on Formulas, when I am able.
I am glad I finally got the R C stylio ( Absolute and Relative Type ) well understood now.