4

I have a UDF(User-Defined Function) in VBA that needs to modify cell range on Excel.

Since a UDF cannot do this, I tried using Event calls.

When I raise a Custom Event and try to write to cells, I get #Value error. On the other hand, Application events such as Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range) can write to cells.

My questions is how do I update other cells by calling a UDF?

Daniel
  • 12,569
  • 2
  • 34
  • 57
user1684104
  • 41
  • 1
  • 1
  • 2
  • 1
    What do you mean by modify cell range? What exactly are you trying to do? – Daniel Sep 19 '12 at 19:47
  • And what do you mean by _functions cannot do this_? – Olle Sjögren Sep 19 '12 at 19:53
  • Functions cannot modify cells, correct ? They can only return values but when we want function execution to actually modify cells, we play a trick by getting help from Application events such as App_SheetChange that can write to cells. Now instead using Application Events, I want to use my own Events( say PrintGrid) that I created using Event keyword but my testing shows that the custom event handler throws error when attempting to modify excel cells. SO my question - Is it possible to modify excel cells from custom event handlers that are triggered manually from functions using RaiseEvent – user1684104 Sep 19 '12 at 20:03
  • I would say a VBA function can modify cells in any way you want. Again, what do you mean by `modify cell range on Excel`? What is it that you want to do with the cell range? Please describe what you want to do, and we'll get to the events if you really need them (it sounds overly complex). – Olle Sjögren Sep 19 '12 at 20:55
  • The function should actually fill data in a range of cells. The formula is =TSS_GETEMPLOYEEDATA which may return data as big as 1.5G. If I try to return grid from the function, I am getting Out Of Memory error, so I am thinking to display the grid in batches using EventHandlers and when all the grid is populated, return True from function. – user1684104 Sep 19 '12 at 21:02
  • @Olle - a function *called from a worksheet* cannot modify the sheet, only return a value (or values if used as an array formula). As long as you're not calling it as a UDF then yes a function can modify the sheet in the same way as a Sub. – Tim Williams Sep 19 '12 at 23:16
  • @TimWilliams Thanks for the explanation, I didn't know about that limitation which is part of why I didn't understand this question. The more you know... – Daniel Sep 20 '12 at 00:01
  • 3
    @DanielCook A UDF called from a sheet can modify the sheet - but it is complex. http://stackoverflow.com/a/8711582/641067 – brettdj Sep 20 '12 at 00:05
  • @TimWilliams OK, thanks, I missunderstood the question. – Olle Sjögren Sep 20 '12 at 07:47

2 Answers2

9

Here is a way you can circumvent the restraint, you must do it indirectly. Method copied from Excel - How to fill cells from User Defined Function?:

In a standard module:

Public triggger As Boolean
Public carryover As Variant
Function reallysimple(r As Range) As Variant
    triggger = True
    reallysimple = r.Value
    carryover = r.Value / 99
End Function

In worksheet code:

Private Sub Worksheet_Calculate()
    If Not triggger Then Exit Sub
    triggger = False
    Range("C1").Value = carryover
End Sub

This could be expanded for your purposes. Essentially, the UDF updates public variables which are then read from the Worksheet_Calculate event to do... anything you like.

Another more complicated approach would be to write a vbscript file from your function that will attempt to automate Excel and run it via Shell. However, the method I listed above is much more reliable.

Daniel
  • 12,569
  • 2
  • 34
  • 57
  • Thanks Daniel and everyone. I used a similar approach but instead of using Workseet_calculate, I used App_SheetChange which will look for function completion event(cell A4 gets True) and then reads the global variable and displays on Excel grid. This works very nice but I would like to have my own event, say PrintGrid( by using Event keyword and raising by RaiseEvent) but the event handler gives error while writing to the range of cells..so my questin - SO my question - Is it possible to modify excel cells from custom event handlers that are triggered manually from UDF using RaiseEvent ? – user1684104 Sep 20 '12 at 15:23
0

If call other function with Application.Evaluate method in your UDF function you can change everything on sheet (Values,Steel,Etc.) because VBA does not know which function is called.

Example: 

Sub UDFfunction()
  Evaluate "otherfunc(""abc"")"
End Sub

Public Function otherfunc(ByVal str As String)
  ActiveSheet.Cells(1, 1).Value = str
End Function
stenci
  • 7,245
  • 11
  • 54
  • 89
Cem Firat
  • 11
  • Can you explain a bit more? – Dieter Meemken May 20 '16 at 10:54
  • Example: Sub UDFfunction() Dim s As string s="Text" Evaluate "otherfunc(""" & s & """)" End Sub Function c() End Function ---------------- Public Function otherfunc(ByVal str As String) ActiveSheet.Cells(1, 1).Value = str End Function --------------- active sheet Cell "A1" Value will be "Text" – Cem Firat May 20 '16 at 11:23
  • This is the best answer, because it does the job without relying on delayed actions triggered by other events. – stenci Mar 16 '20 at 20:50