Use Excel Formulas/Functions In VBA Macro Code

  • <p class="j"><b>SEE ALSO:</b> <a href="http://www.ozgrid.com/News/excel-evaluate-formula-VBA.htm">Excel 4.0 EVALUATE Function</a></p><h2>Excel VBA WorksheetFunction Property</h2><p class="j">As you may, or may not know, we can use standard <a href="http://www.ozgrid.com/Excel/">Excel Worksheet Functions</a> in VBA by preceding the Function name with <b>WorksheetFunction</b> or <b>Application</b>. E.g. the example below will sum the range A1:A10.</p>






    <p class="j">However, IF there ANY <a href="http://www.ozgrid.com/Excel/formula-errors.htm">Formulae Errors</a> in the range used, it will result in a <a href="http://support.microsoft.com/kb/146864">RunTime Error</a> so you may want to replace all error cells with zero, or at least confirm the range has no formula errors with the <a href="http://www.ozgrid.com/VBA/special-cells.htm">SpecialCells Method</a>, like below;</p>



    <h2>Excel VBA Evaluate Method</h2><p class="j">With the Evaluate Method, we must still check for, or fix, formula errors in the range we Evaluate with an <a href="http://www.ozgrid.com/Excel/">Excel Formula</a>, but there is less typing and we can simply copy Formulas from the formula bar. The <a href="http://www.ozgrid.com/VBA/">VBA Macros</a> below shows some uses of the Evaluate Method, BUT contain no error checks. <b>Don't forget to add them like above</b>.</p>


  • Re: Use Excel Formulas/Functions In VBA Macro Code


    I need help. I have a column of cells and I'm trying to find the cell with the lowest number greater than zero. I can't figure out a formula to get the results I need.