Excel VBA Basic Training

Excel VBA Basic Training

Formulas

Absolute Formula – Normal excel formula syntax same as what is in the spreadsheet cell formula. Does calculation on the front end spreadsheet side of Excel.

ActiveCell.Formula = "=Sum(A1:A4)"
ActiveCell.Value= "=Sum(A1:A4)"

Relative Formula – Instead of using cell references in the formula, i.e. A1:A4, the relative formula uses row and column offset from the current cell, i.e. r[-4]c:r[-1]c. Example, r[-4]c refers to the cell 4 rows above the active cell in the same column. r[row offset]=r[-4]=4 rows above active cell. r[-1]c refers to the cell 1 row above the active cell in the same column. r[row offset]=r[-1]=1 row above active cell.

ActiveCell.FormulaR1C1 = "=Sum(r[-4]c:r[-1]c)"

WorksheetFunction – Excel functions calculated from the VBA editor side and not the spreadsheet side. Value from formula can be assigned to a variable or the calculated value can be inserted directly into the spreadsheet. Can be thought of as a backend calculation formula as opposed to normal front end excel formula.

dim sumValue as integer
sumValue = Application.WorksheetFunction.Sum(Range("A1:A4"))

[efb_likebox fanpage_url="https://www.facebook.com/vbamarket/" box_width="840" box_height=340"" responsive="1" show_faces="1" show_stream="1" hide_cover="1" small_header="0" hide_cta="0" locale="en_US"]

Add comment