0

I'm using the trick described here - !A1 - to get the the range of cells up to and including the current one (typically for a Rows() function). This works great for day to day usage, and eliminates a lot of errors that I get when moving ranges around when I previously had to use an adjacent set of rows.

Unfortunately, my formulas need to be evaluatable from VBA. With __THISCELL__ as my !A1 cell, and the cell housing the formula as $Z$100 the following evaluates to an error:

Application.Evaluate(rngCell.formula)

And the following evaluates to $A$1:$Z$50 

rngCell.Worksheet.Evaluate(rngCell.formula)

Obviously an approach is to replace __THISCELL__ with rngCell.Address(External:=True) prior to evaluation, but here's the kicker: I'd like to be able to execute my formula parser in a workbook which uses, say THIS_CELL, THISCELL or __THISCELL safely, and I'd also like to be able to safely execute my code in a workbook with a name like __NOT__THIS_CELL__.

All I need for this is a mechanism to evaluate relative references relative to a specific cell address - which since people do use R1C1 references in VBA a fair bit, I imagine must be around. However, I don't know it. Can anyone help?

NB: I like to avoid fiddling with ActiveCell, Selection, etc. where possible, since those smell like the excel equivalent of SendKeys - who knows what the user is doing when you access them. Even then, though, I'm not certain I'll get the right answer, because for the Worksheet.Evaluate approach, I'm not positioned in cell $A$1!

Community
  • 1
  • 1
tobriand
  • 1,095
  • 15
  • 29
  • AFAIK `Evaluate` always resolves relative named ranges with respect to `A1`, so you won't be able to calculate correct values for other cells. Why do you need to manually parse formulas? – BrakNicku Jun 08 '15 at 07:18
  • We use `CUBEVALUE` a lot, and I need to be able to extract nfrom an arbitrary formula the MDX passed to generate the constituent `CUBEMEMBERS` and `CUBESET`s that we pass in to execute the query. `CUBEMEMBERS` aren't too evil - there is an `.mdx` property that shows the member by index (though e.g. if you get there by `FILTER()` then that gets lost), but `CUBESET`s don't have a working `.mdx` property, so need to find the argument that was passed to the function. Which since it could be found via any number of `INDEX`, `VLOOKUP` or named range process, basically means formula parsing! – tobriand Jun 08 '15 at 10:28
  • Could please provide a more detailed sample of what you have done, and the expected results. Also explain what is the difference between `THIS_CELL`, `THISCELL`, `__THISCELL` and `__NOT__THIS_CELL__`. It's correct to assume that `rngCell` refers to a name equivalent to `THIS_CELL` as described in the question referred at the beginning? – EEM Jun 08 '15 at 12:23
  • Well, the idea is that a user could define any name that had the content of `!A1` (or `!RC`). Those names are just a number of names that they could use. They could just as easily define it as `Scotland`, `Dragons` or `FooBar`, and I wouldn't want my code to break on it! – tobriand Jun 08 '15 at 16:29

2 Answers2

0

If I understand your question, I believe you're looking for the Range().Offset method.

Range().Offset(rOffset, cOffset) refers to a range that is rOffset lower and cOffset to the right of the given range (negative values for up and left are allowed). Also, .Offset can access and set all of the properties of the range, just like you would do with .Range.

nwhaught
  • 1,562
  • 1
  • 15
  • 36
  • I'm afraid not - that will let me go from one range a specified distance via X and Y to another. What I'd like to do is take some text that constitutes a formula and evaluate it as though it were entered in a known cell. To see the issue, put the cursor in, say, `A1` and create a new named range. In the refers to box, enter `=$B1`. Now in cells `A1:A5` enter your name, and put some stuff in column B. Note how even though the formulae are the same, they always refer to the cell to their right? Now, I know I'm working with a formula from `A5`, but Excel doesn't! The problem is how to tell it! – tobriand Jun 07 '15 at 18:19
  • @tobriand this do not always refers to the right, it always refers to column `B` – EEM Jun 08 '15 at 11:51
  • @EEM - might be I'm using `Worksheet.Evaluate` instead of `Application.Evaluate`. I'll check my code and let you know. – tobriand Jun 08 '15 at 16:31
-1

The approach I've taken for the time being is implicit in the question: when a named range is detected, store the current selection and worksheet, select the one which we use as the evaluation context, and then use Evaluate. This seems to work, provided the cell being evaluated is inside the activesheet.

I don't like jumping the selection all over the place - feels dirty - but short of a more elegant solution, it does work.

tobriand
  • 1,095
  • 15
  • 29
  • With the worksheet configured as you described in your comment below other answer, `Evaluate` always returns `B1` value, no matter which worksheet or cell is currently active (at least in 2010). And `rngCell.Worksheet.Evaluate(rngCell.formula)` should give the same result as `Application.Evaluate(...` for active sheet. – BrakNicku Jun 08 '15 at 10:43
  • Hmm... well, it's definitely working for me, so I'll check again exactly what I've done. I might be using `Worksheet.Evaluate` instead of `Application.Evaluate`, which sometimes does a better job of dealing with relative ranges with respect to the current worksheet. – tobriand Jun 08 '15 at 16:30
  • `Application.Evaluate` is an equivalent of `Activesheet.Evaluate` – BrakNicku Jun 08 '15 at 16:36
  • No, not quite. They're definitely similar, but if you create a UDF with a breakpoint and pass it to `Application.Evaluate`, it gets called twice, whereas `Worksheet.Evaluate` only gets called once. There's a nice post on the issue, focusing on efficiency of the statement at https://fastexcel.wordpress.com/2011/11/02/evaluate-functions-and-formulas-fun-how-to-make-excels-evaluate-method-twice-as-fast/. I'm certain there's also differences in how they treat names, but can't remember the details. – tobriand Jun 09 '15 at 10:51