Excel VBA: Call Sub procedure after executing a function

Question

In myFunction, I want to create a log in a sheet with the arguments of the function and the time it was last executed.

My code is like this:

Function myFunction(arg1, arg2 As String)

   //code here

   Sheets("Sheet1").Range("A1") = arg1
   Sheets("Sheet1").Range("B1") = arg2
   Sheets("Sheet1").Range("C1") = datetime.Now

End Function

This function doesn't work with the last 3 lines (it does otherwise).

I also tried to create a separate module for this task:

Function myFunction(arg1, arg2 As String)

       //code here

    Call myLog(arg1,arg2)

    End Function

But it doesn't work either

Public Sub myLog(arg1,arg2)
   Sheets("Sheet1").Range("A1") = arg1
   Sheets("Sheet1").Range("B1") = arg2
   Sheets("Sheet1").Range("C1") = datetime.Now
End Sub

Thanks in advance for helping!

Does the code give you an actual error, or does it run, but not fill in the cells? — Blackhawk
– Blackhawk, Commented Nov 23, 2013 at 19:09
@Blackhawk it doesn't run. It just returns #VALUE! error when I type the function in a cell — user882670
– user882670, Commented Nov 23, 2013 at 19:10

Community · Accepted Answer · 2017-05-23 10:25:40Z

1

"It just returns #VALUE! error when I type the function in a cell".

A function can only modify the cell which it is called from. So you will get an error when you try to modify other cells.

Here are some references that might be useful:

Making Excel functions affect 'other' cells

VBA - Update Other Cells via User-Defined Function

http://www.vbforums.com/showthread.php?508759-Counting-Particular-Letter-Occurrences-in-a-String

edited May 23, 2017 at 10:25

CommunityBot

11 silver badge

answered Nov 23, 2013 at 19:28

Roberto

2,2046 gold badges33 silver badges50 bronze badges

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

1 Comment

user882670 Over a year ago

Considering all the dependencies of the UDF (it might affect several other cells that depend on it) it doesn't seem to make sense to do this. Perhaps pushing the data to an array would work better (consume less resources). Thanks for your answer

Collectives™ on Stack Overflow

Excel VBA: Call Sub procedure after executing a function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related