Excel VBA use range as input

Question

I'm in the process of learning Excel VBA, and am trying to create a simple function.

The idea is that I would use a cell as an input, and the function would get tell you the standard deviation of 12 values placed around that cell.

So if I type getstd(A1), it would get me the standard deviation of A1, A3, A5, A7, C1, C3, C5, C7, and E1, E3, E5 and E7.

If I type getstd(X23), it would get the std of 12 other values placed at the same offsets around X23.

My biggest problem right now is figuring out how to use a cell as an imput.

For example, when experimenting with offsetting :

Function getstd(rng as Range)

     Range(rng).Offset(1,1) = "hello world"

End Function

It always gives me a #Value error.

I feel that if I can get that to work, then creating my function should be easy.

Thank you!

Michael

Though what @findwindow said is correct syntax, A UDF will not change the value of a different cell than the caller, (without some funky sidestepping). To learn about offset change the line to `getstd = rng.offset(1,1).value` it will return the value one row down and one column over from the specified range. — Scott Craner, Dec 29 '15 at 19:52
@findwindow Just tried. Still gives me a #Value error. In cell W23, I type `=getstd(X12)`, and I expect it to go put "hello world" in cell Y13, but it doesn't... — michaelhfwu, Dec 29 '15 at 19:55
[This answer](http://stackoverflow.com/a/24222666/2165759) may be helpful. — omegastripes, Dec 29 '15 at 21:18

score 0 · Answer 1 · answered Dec 29 '15 at 23:21

You can group (Union) the cells into one multi-area range and use the builtin stdev function:

Function getstd(ByVal target As Range)
    Dim r As Range
    For i = 0 To 6 Step 2
        For j = 0 To 4 Step 2
            If r Is Nothing Then Set r = target.Offset(i, j) Else Set r = Union(r, target.Offset(i, j))
        Next
    Next
    getstd = Application.StDev(r)
End Function

Darren Bartrup-Cook · Answer 2 · 2015-12-31T11:37:42.377

Another way to do it:

Public Function STD_DEV() As Variant

    Dim rDataSet As Range
    Dim rCell As Range
    Dim aValues(1 To 8) As Double
    Dim x As Long

    Application.Volatile

    With Application.ThisCell
        'Check we're not trying to reference off the sheet.
        If .Row > 1 And .Column > 1 And _
            .Row < Rows.Count And .Column < Columns.Count Then

            'Get a reference to all cells around target cell.
            'This includes the target cell in its reference.
            Set rDataSet = .Offset(-1, -1).Resize(3, 3)

            'Step through each cell in the range and add
            'value to an array.
            x = 1
            For Each rCell In rDataSet
                If rCell.Address <> .Address Then
                    aValues(x) = rCell.Value
                    x = x + 1
                End If
            Next rCell

            'Calculate the Standard Deviation.
            STD_DEV = WorksheetFunction.StDev(aValues)

        Else

            STD_DEV = CVErr(xlErrRef)

        End If
    End With

End Function

Note the WITH keyword - anything between WITH and END WITH that starts with a . refers to Application.ThisCell. So .Row is the same as saying Application.ThisCell.Row

Edit: Have updated the function to return a #REF! error if it tries to reference off the sheet.

Excel VBA use range as input

2 Answers2