Excel VBA: Insheet function code can not access other cells on sheet

Question

I'm having some issues with an insheet function that I am writing in VBA for Excel. What I eventually am trying to achieve is an excel function which is called from within a cell on your worksheet, that outputs a range of data points underneath the cell from which it is called (like the excel function =BDP() of financial data provider Bloomberg). I cannot specify the output range beforehand because I don't know how many data points it is going to output.

The issue seems to be that excel does not allow you to edit cells on a sheet from within a function, apart from the cell from which the function is called.

I have created a simple program to isolate the problem, for the sake of this question.

The following function, when called from within an excel sheet via =test(10), should produce a list of integers from 1 to 10 underneath the cell from which it is called.

Function test(number As Integer)

For i = 1 To number
Application.Caller.Offset(i, 0) = i
Next i

End Function

The code is very simple, yet nothing happens on the worksheet from which this formula is called (except a #Value error sometimes). I have tried several other specifications of the code, like for instance:

Function test(number As Integer)

Dim tempRange As Range
Set tempRange = Worksheets("Sheet1").Range(Application.Caller.Address)

For i = 1 To number
tempRange.Offset(i, 0) = i
Next i

End Function

Strangely enough, in this last piece of code, the command "debug.print tempRange.address" does print out the address from which the function is called.

The problem seems to be updating values on the worksheet from within an insheet function. Could anybody please give some guidance as to whether it is possible to achieve this via a different method?

Thanks a lot, J

Possible duplicate of [Using a UDF in Excel to update the worksheet](http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) — Comintern, Oct 25 '16 at 17:02

Charles Williams · Answer 1 · 2016-10-25T17:37:09.323

User defined functions are only allowed to alter the values of the cells they are entered into, because Excel's calculation method is built on that assumption.

Methods of bypassing this limitation usually involve scary things like caching the results and locations you want to change and then rewriting them in an after calculate event, whilst taking care of any possible circularity or infinite loops.

The simplest solution is to enter a multi-cell array formula into more cells than you will ever need.

But if you really need to do this I would recommend looking at Govert's Excel DNA which has some array resizer function. Resizing Excel UDF results

score 0 · Answer 2 · answered Oct 25 '16 at 17:43

Consider:

Public Function test(number As Integer)
    Dim i As Long, ary()
    ReDim ary(1 To number, 1 To 1)

    For i = 1 To number
        ary(i, 1) = i
    Next i

    test = ary
End Function

Select a block of cells (in this case from C1 through C10), and array enter:

=test(10)

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

Excel VBA: Insheet function code can not access other cells on sheet

2 Answers2