2

I currently have a macro that uses a form in order to perform some calculation with the help of an external program. All it does is writing some worksheet values to a file for use by another program, an external .exe, as input (that program is closed source). Said program then writes a custom format output, I scrap it with VBA and then pass the return back to the worksheet. The macro is invoked via a userform where the source and destination ranges are specified.

What I'm trying to do is to replace the method of invoking the calculation via a userform with a UDF. With the GUI method, updating the calculation is cumbersome; it is also impossible to know what computation was performed in the destination range data. This path was chosen over the UDF because of performance concerns. Since the calculation is quite slow, I can't just reuse the part of the userform code that invokes the external program as a UDF and be done with it, since it would be way too slow. There seems to be no async execution in VBA (as opposed to xll's in Excel 2010).

A possible solution would be to exit the UDF as soon as it begins executing unless a global is set to true. That global would always be false, unless a recalc is invocated from a specific ribbon button. The problem is that this executes constantly UDFs, sending those ranges to #N/A values all the time, so I can't reference to those ranges. Another would be to create fake formulas, ie a comment on the cell that specifies both the parameters and the destination range. This too has many problems.

So. Any idea on how to implement a fake-async calculating UDF?

Thanks!

Community
  • 1
  • 1
s_a
  • 885
  • 3
  • 9
  • 22
  • I am puzzled. You talk about a UDF AND about a GUI? I don't understand this? – Robert Ilbrink Jan 05 '12 at 18:20
  • I don't understand this either. There seems to be some confusion about the meaning of "UDF". – Jean-François Corbett Jan 06 '12 at 07:43
  • Currently the off-excel computation is done via a GUI. A form is show where you select the origin and destination ranges with a refedit control. What I'm trying to do is to replace the GUI with a UDF. With the GUI method updating the calculation is cumbersome; it is also impossible to know what computation was performed in the destination range data. This path was chosen over the UDF because of performance concerns. Thanks for commenting and sorry for the confusion. – s_a Jan 06 '12 at 22:44

1 Answers1

2

If I understand you correctly you want to have a VBA UDF that returns the value from the previous calculation if the global is false, otherwise do the full slow calculation.
There are several different possible approaches to this, which all have different drawbacks:

  • use application.caller.value. This will cause a circular reference and require you to switch on iterative calculation
  • use application.caller.text. This is simple and works without iterative calculation but gets the formatted value so you need to be sure that your UDF does not lose precision etc
  • at udf exit store the value in a global array indexed by the full calling cell address and at udf entry retrieve it. This will not persist in a saved workbook unless you store the global array somewhere at save/close and retrieve on open.
  • Write an XLL or XLM macro-equivalent UDF that retrieves the previous value
Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • I think that the way to go is by using `application.caller.text`. Although it will still execute many times needlessly, it seems like the best call. I was not aware that you could do that. Here is another reference I found after seeing your post. http://www.dailydoseofexcel.com/archives/2011/11/30/udf-for-cumulative-sum/. Seems that "always go for .Value2 instead of .Text" wasn't exactly right. Thanks! – s_a Jan 06 '12 at 22:59
  • 1
    .Text is useful as long as you can live with the formatted value. See http://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/ for a discussion of .Value vs .Value2 vs .Text – Charles Williams Jan 07 '12 at 00:17
  • The `Application.Caller.Text` trick doesn't work on arrays, right? This is only useful for single cell parameters. – s_a Jan 08 '12 at 14:10
  • Application.Caller returns the range that the UDF occupies, so if the UDF is entered into multiple cells as an array formula you can get the .TEXT property of each of the cells using application.caller.cells(j,k).text or similar – Charles Williams Jan 08 '12 at 14:42
  • I doesn't seem to be possible: `Function test(); test = 0; Debug.Print Application.Caller.Cells.Count; Debug.Print Application.Caller.Address; For i = 0 To Application.Caller.Cells.Count; Debug.Print i; Debug.Print Application.Caller.Cells(i).Address 'THIS LINE FAILS; Next; End Function` The line in question exits the UDF without error. (No multile code in comments). – s_a Jan 08 '12 at 15:17
  • 1
    Function Async5(vKey, Refresh); Dim vOut() As Variant; Dim j As Long; Dim nRows As Long; Dim k As Long; Dim nCols As Long; Dim oCaller As Range; Set oCaller = Application.Caller; nRows = oCaller.Rows.Count; nCols = oCaller.Columns.Count; ReDim vOut(1 To nRows, 1 To nCols); For j = 1 To nRows; For k = 1 To nCols; If Not Refresh Then; vOut(j, k) = Val(oCaller.Cells(j, k).Text); Else; vOut(j, k) = Rnd; End If; Next k; Next j; Async5 = vOut; End Function; – Charles Williams Jan 08 '12 at 16:20