Worksheet Formula in VBA Part II

If you read Part I, you know that I’m a cantankerous old coot whose set in his ways. Well, it’s not as bad as all that. One reason that I never converted to writing worksheet formulas in VBA using R1C1 is that I don’t really write them.

I created this utility so that I could write the formula in a cell, then convert it to a string and paste it into my code. It has the added benefit of allowing me to see if the formula works first. Of course, that doesn’t insure that the utility works, but it hasn’t failed me yet. If you try it and it doesn’t work, let me know.

Note that the code uses the Precedent object to see which cells are in the formula. Because Precendents doesn’t work properly for references to other sheets, this may not work as expected if you have formulas that reference another sheet.

The output of the code is to print the string to the Immediate Window. Then I copy and paste it to the appropriate place in my code. It assumes that my formula will be relative to a range variable named “cell.” If you use a different variable name, do a Find and Replace after you paste.

17 thoughts on “Worksheet Formula in VBA Part II

  1. I’m still enjoying your blog…….
    but one comment/question… when I click on a calendar date I seem to get to the comments section of the first blog entry of that day…
    Or am I doing something wrong?

  2. Glad to hear it Wendy. I don’t think you’re doing anything wrong, that’s just how it works. It takes you to the first Permalink for that day and you can use the links that the top to navigate. I don’t like it either.

    I might try archiving by week instead of by day. What do you think of that?

  3. Wendy: I changed the archiving to weekly and added the weekly archive links to the side bar. I like it better, but it’s still not perfect. Your thoughts are appreciated. Thanks.

  4. OK, you’ve done it. After reading your blog for the past few weeks, I’m going to learn VBA. I figure that, between your blog and JWalk’s book, even a tyro like me should be able to do some useful things in VBA.

    This snippet of code looks particularly useful. I’m even more impressed with it than I am with spelling the word “Aegilops” using the column headers. (No offense, JWalk).

  5. Hi,

    I am working in finding the Precedents of a given cell.I am able to find the Precedents in the Active Worksheet. Is it possible to find the precedents of a given cell in an Inactive Worksheet?

    Plz advise.

    Thanks,

    Cheers,
    anantha

  6. Dick,

    Thank you for this code, i really like your code.

    I didn’t manage to copy paste it to my own project, because “”” gave an error:

    sForm = Chr$(34) & Replace(sForm, Chr$(34), “”” & chr$(34) & “””) & Chr$(34)

    It seems to work after several changes. It might be a good idea to post an Excel document with the code inside to avoid this problem.

    Best Regards,
    Vincent

  7. Dick,
    I don’t know if I’ve missed the point, but what I do is write the formula in a cell (and test it), then I turn on the macro recorder, F2 (edit) and ENTER to make the recorder think it recorded me writing the formula, then turn off the recorder. The formula is written in R1C1 notation in my new macro. I then copy to my code. Since I only do this twice in a blue moon, no utility required.

    Facile? Bien Oui! Thoughts?

  8. Thanks Dick. (Or does “Nice Work” mean “Thanks – just in the nick of too late”?)
    April 2004 I was probably in beautiful Sarnia, Ontario, admiring the majestic refineries there.

  9. No. There was no sarcasm there. I still need a way to automate this more. Starting the recorder, stopping the recorder, copying, pasting, and deleting a module is just too much work. How do I get into the macro recorder code directly?

  10. Well, Dick, it turns out that I WAS missing something. My past experience was is getting a string which represented a cell formula, and re-using it – as in:
    sFormula = “=SUM(RC[-1]:R[1]C[-1])”
    rng.formulaR1C1 = sFormula

    Obviously (at least it is obvious to me now), you want to acquire the cell formula and turn it into a VBA equation which computes in code.

    So – sorry I misunderstood, and, if this could be fully automated as you intend, then this would be a great way to have business rules written outside of VBA. I would love that! Right now I use a big class module with lots of properties for one of my major calculation engines. At least its centralized.

    I have no experience with automating macro recorder.

  11. I think I’m missing something here. I’m not getting any output from this code. It runs through and processes the formula, but there’s nothing at the end for me to paste into my code except the original formuala.

  12. On another note, I don’t really use this method any more. I should probably blog about it. I’ve converted (kicking and screaming, mind you) to using R1C1 in code. I write the formula in the cell and select it. Then I go to the immediate window and type:

    ?activecell.formular1c1

    Take that result and put it in my code like:

    rCell.FormulaR1C1 = “=IF(RC[-4]< =RC[-1],RC[-4],99000+RC[-1])”

    Works a peach, except for interpreting the formula in code.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.