Writing into Excel worksheet from custom VBA function (not sub)

Question

I would greatly appreciate your assistance in helping me write data from a custom VBA function into an excel worksheet. I have been able to write from a custom VBA subroutine but get an error whilst executing a VBA function. In the sample code, "Sub write 2" and "Sub write 3" work just fine. But "Function test 2()" generates "Value!" in its cell and write3 isn't executed.

Sub write2(r As Integer, c As Integer, d As Double)
    Dim a, b As Integer
    For a = r To r + 1
        For b = c To c + 1
            Cells(a, b).Value = d
        Next b
    Next a
End Sub

Sub write3()
    Call write2(3, 60, 0.437)
End Sub

Function test2() As Double
    Call write3
    test2 = 1#
End Function

possible duplicate of [Cannot VBA write data to cells in Excel 2007/2010 within a funtion](http://stackoverflow.com/questions/9476282/cannot-vba-write-data-to-cells-in-excel-2007-2010-within-a-funtion) — brettdj, Dec 25 '13 at 07:28
It is actually possible - albeit complex - see the link above which is the same request you have — brettdj, Dec 25 '13 at 07:29
Hello @brettdj: Thank you! Looks like I did have the same question. Unfortunately, the work around seems to introduce a lot of complexity in the code. So, I'll be converting the function into a subroutine. — user3133655, Dec 25 '13 at 08:29
@user3133655: A subroutine is indeed the best way to go. Personally, I'd write one Sub and one Function here. The Sub goes through my data, feeds it into my Function, and the Function returns the value to the Sub which outputs it into wherever I want. This is assuming, of course, that you absolutely have to use a Function. Otherwise, one big Sub is going to cut it (or multiple small subs calling each other). ;) — WGS, Dec 25 '13 at 10:03

score 3 · Answer 1 · edited May 08 '18 at 21:48

3

As per Chip Pearson's great website:

A UDF can only return a value to the cell(s) whence it was called -- it must not modify the contents or formatting of any cell and must not modify the operating environment of Excel. If you attempt to change anything, the function will terminate immediately and return a #VALUE error to the calling cell.

edited May 08 '18 at 21:48

Community

1
1

answered Dec 25 '13 at 01:52

Lance Roberts

22,383
32
112
130

Hello @Lance: Thank you. In this case, can you please help me understand how to transfer data from an UDF to multiple cells of an excel sheet? – user3133655 Dec 25 '13 at 04:29
@user3133655, you'd really have to explain why you'd need it, the context is important for the solution. You should probably create a different question based on what you want to accomplish (but be specific). – Lance Roberts Dec 25 '13 at 04:31
Then use normal controls and normal subs and functions, no need for a UDF. Just tie it all to a button (or slightly more work to an event). – Lance Roberts Dec 25 '13 at 04:40
Hello @LanceRoberts: I'm trying to accomplish the following: (1) Read data from certain rows into an excel function (2) Manipulate data in function (3) Output data onto excel sheet. Constraints are: (i) The function is executed on each row (ii) Input data is from that row and all preceding rows. Some alternative solutions I've thought of would be to output data into a text file OR write a subroutine (instead of a UDF) but I didn't think these two were as elegant. Does this help? – user3133655 Dec 25 '13 at 04:42
Actually, in this case the UDF is not elegant. Use a normal subroutine called by some Active-X control. – Lance Roberts Dec 25 '13 at 05:10

Writing into Excel worksheet from custom VBA function (not sub)

1 Answers1