Search notes:

Excel: passing the value of a cell to a VBA function

Excel allows to call a custom VBA functions from a worksheet and display the function's return value in a cell. In order to do that, the cell's formula needs to be set to =vbaFuncName().

It's also possible to pass values from other cells to the function. In that case, the function needs to have one (or more) Range object parameters. This is demonstrated in the following example:

option explicit

sub testData() ' {

    cells (1, 1) = "foo": cells (1, 2) = 4: cells(1, 3).formulaR1C1 = "=repeatString(r[0]c[-2], r[0]c[-1])"
    cells (2, 1) = "bar": cells (2, 2) = 1: cells(2, 3).formulaR1C1 = "=repeatString(r[0]c[-2], r[0]c[-1])"
    cells (3, 1) = "baz": cells (3, 2) = 3: cells(3, 3).formulaR1C1 = "=repeatString(r[0]c[-2], r[0]c[-1])"

end sub ' }

function repeatString(cellText as range, cellTimes as range) as string ' {

    dim i as long
    repeatString = cellText
    for i = 2 to cellTimes
        repeatString = repeatString & " " & cellText
    next i

end function ' }

Github repository about-MS-Office-object-model, path: /Excel/Range/pass-cell-value-to-function.bas

After executing testData(), the worksheet is filled as follows:

Excel: passing the value of a cell to a VBA function

See also