Distinguising different formulas

  • Hi all,

    Anyone able to help me with the following:

    I have a sheet with various formulas in the different cells. I would like VBA to recognize cells that contain formulas ONLY with:
    1. the normal mathematical operators (e.g. + - / * =) and;
    2. hard numbers (e.g. 40 20 etc.)

    I think I need some code to get the entire formula into a variable and then let VBA figure out what formula applies to the criterion and what does not.

    Many thanks in advance!


  • Re: Distinguising different formulas

    A couple of observations.

    1). Most cells containing a formula will contain "=" - so do you really want to include this in your criteria?

    2). What constitutes a hard number (in your terms) from any other sort of number ?

  • Re: Distinguising different formulas

    True the "=" is trivial, since it is included in all formulas.

    With hard number I mean just any real number. This compared to cell references.

    I am thinking something like: code to put the respective formulas into a string variable and then letting VBA go through all the characters in the string to determine strings that contain letters (which must be cell references) and strings that do not contain them (which must be formulas that contain only these hard numbers).

  • Re: Distinguising different formulas


    Try this to test whether a range(cells) has formula in it.


    And for numbers:
    Range.SpecialCells(xlCellTypeConstants, 1).Select


  • Re: Distinguising different formulas

    Can you provide an explanation of why you are doing this? It seems like fairly complex process and there may be other ways to accomplish it.

    By way of complexity, what if there is text in the formula that includes a basic operator or a cell reference that is only text and not actually used in the computation. Do you care about the SUM function which could be looked at as simple addition?

  • Re: Distinguising different formulas

    I have a sheet consisting of I) numbers, II) formulas of the type: =44+55+66 and III) other formulas.

    I want to be able to multiply I as well as II but not III. I would like to do this by selection an area with my mouse and then running the code.
    But I think I have already found a way. Im am going to add the ' sign to all of the formulas and then let VBA browse through the string to see if there is any letter from the alphabet in it. If not, it must be a formula of type II and then I can simply append *100 or something to it. Finally I will have to remove the ' sign and I should be all set.....

  • Re: Distinguising different formulas

    I developed a tool for myself like that once.

    Basically I wanted to recognize hard input formulas (ie: =100+25+74) the same as user input values. It's obvious to the human brain that the hard input formula is merely a user input where the user may want to keep track of what values built up to the total. It's not so obvious to Excel.

    The logic I employed used the specialcells method to first select all the formulas on the sheet. Then I stepped thru each cell with a for/each and tested the formula for a direct precedent (cell.DirectPrecedents). The idea being that a formula without precedent cells must be a user hard input formula as mentioned above.

    I did have to include some tests to make sure some standalone functions weren't being captured. For instance the use of TODAY, NOW, ROW, COLUMN, RAND, CURRENTITER, CURRENTSIM would not require a precedent cell. If you can think of any others that don't require a precedent add em to my list.

    Hmmm... you might be right though, perhaps a more efficient test would just involve searching for an alpha character in the formula string.

    It's worked fairly well for me over the years. But now I'm tempted to revisit it to see if the alpha search is faster.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!