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 — brettdj
– brettdj, Commented Dec 25, 2013 at 7:28
It is actually possible - albeit complex - see the link above which is the same request you have — brettdj
– brettdj, Commented Dec 25, 2013 at 7: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
– user3133655, Commented Dec 25, 2013 at 8: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
– WGS, Commented Dec 25, 2013 at 10:03

Community · Accepted Answer · 2018-05-08 21:48:18Z

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 8, 2018 at 21:48

CommunityBot

11 silver badge

answered Dec 25, 2013 at 1:52

Lance Roberts

22.9k32 gold badges115 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3133655 Over a year ago

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?

Lance Roberts Over a year ago

@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 Over a year ago

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).

user3133655 Over a year ago

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?

Lance Roberts Over a year ago

Actually, in this case the UDF is not elegant. Use a normal subroutine called by some Active-X control.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related