Excel XLL UDF

Add-in Express™ Support Service
That's what is more important than anything else

Excel XLL UDF
Skip calculation of UDF 
Posts 1 - 10 of 15
First | Prev. | 1 2 | Next | Last 
Michael Kaden


Guest


Hello,

This applies to "calculate" command i.e. all cells, also These with no dependencies, are calculated.

I want to skip the calculation of a specific UDF defined in the XLL Module with the use of a global constant "NoCalc". If "NoCalc" is true then the cell UDF shall not be calculated but re-use the current cell value. If NoCalc is False then the UDF shall be calculated.

It looks like I cannot read the value of the calling cell. Range("Callingcell").value Returns 0.

Is there any way how I can subscribe the current cell value to the cell with the calling UDF?
Alternatively, can specific UDF names be excluded from the calculate comand?

Thank you & regards

Michael
Posted 10 Jan, 2018 07:40:43 Top
Michael Kaden


Guest


Hello,

I have another query in relation to the above.

The worksheet.calculate method does not work,I have the following code


Public Sub Renew()
On Error GoTo Errorhandler
Dim xlBook As Excel.Workbook
xlBook = GetActiveWorkbook()
If xlBook IsNot Nothing Then
Dim wSheets As Excel.Sheets = xlBook.Worksheets
For iSheet As Integer = 1 To wSheets.Count
Dim wks As Excel._Worksheet = CType(wSheets.Item(iSheet), Excel._Worksheet)
MsgBox("Worksheet " & wks.Name)
wks.Calculate()
Marshal.ReleaseComObject(wks) : wks = Nothing
Next
End If

End Sub


perhaps the MS note

[URL=https://www.google.es/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&cad=rja&uact=8&ved=0ahUKEwjio-mt-M_YAhUHnRQKHbnGB5YQFggqMAE&url=https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F144508%2Fusing-calculate-method-may-not-calculate-certain-formulas&usg=AOvVaw0rx1opq8VRrpNKexNiaX3c/URL]

shows the problem. I am using Windows 7 and I would like my software to be usable with Windows 7.

as fix I use My.Computer.Keyboard.SendKeys("^%" & "{F9}", True)

which is slow. Any other way to do sheet calculation?

Thank you & regards

Please also reply to the earlier post

Michael
Posted 11 Jan, 2018 08:25:47 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Michael,

Consider storing the last value claculated by that UDF for a given cell and returning it.

Michael Kaden writes:
perhaps the MS note


That page seemingly relates to Office 97!

Can I have a workbook and UDF that I could use to reproduce the issue? If yes, please send them to the support email address.


Andrei Smolin
Add-in Express Team Leader
Posted 11 Jan, 2018 08:34:12 Top
Michael Kaden


Guest


Dear Andrei,

I come back to:

Can I have a workbook and UDF that I could use to reproduce the issue? If yes, please send them to the support email address.


I have made an example project "TestCal12" and Workbook "TestCal12.xlsm" which I will send to the support email.

This is only about the worksheet.calculate method not working.

The issue "DO NOT USE Excel Objects in the XLL context" will be handled in a separate post.
The issue of skip calculations, I will not use.

Now to worksheet.calculate not working

There 1 UDF alera2 which has no parameters therefore is not calculated in the Excel Dependency

A worksheet.calculate call, however, should calculate alera2

If calculation of all the functions in the worksheet is initiated, then alera2 passes the Caller Address to the ComAddIn function "Calc2" which then retrieves (Caller)range.Offset(1,0).Value adds 2.000 and returns the value to alera2.

I initiate the worksheet calculate with Ribbon Buttons

Tab = alera
Group = Test

Button = Key F9
This runs a SendKeys Ctr/Alt/F9
The Cell $B$4 is calculated as intended

Button = Calculate
Loops through all worksheets (I tested this and it works)

it should execute wks.Calculate() - THIS DOES NOT WORK

kindly advise what is wrong.

Thank you and kind regards

Michael
Posted 14 Jan, 2018 08:36:02 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Michael,

Michael Kaden writes:
it should execute wks.Calculate() - THIS DOES NOT WORK


Because your function doesn't *explicitly* depends on a cell, Excel doesn't include the cell containing your function in the calculation tree. On "explicitly": if your function is declared as "=yourfunction(somecell)" and you update that somecell, pressing F9 will call your function and update the cell that refers to your function (and also the cells referring to that cell).

Please have a look at http://www.decisionmodels.com/calcsecretsc.htm.


Andrei Smolin
Add-in Express Team Leader
Posted 15 Jan, 2018 06:51:05 Top
Michael Kaden


Guest


Dear Andrei,

thank you for the link to calcsecrets.If I understand it right,it says:

Note that if you put a false dependency in the argument list (a reference which is not actually used inside the function) Excel will execute the function, but not neccessarily in the sequence you expect.


Which, if I understand this correct, if I change my function alera2 to alera2($A$1) but the value of $A$1 is not used in the UDF, the UDF should still be calculated with worksheet.calculate. This is however not so!

I send you the change project and the changed workbook to the support email address.

I understood now that worksheet.calculate equals F9, but is there a calculate method which does the same as Ctr/Alt/F9 instead of F9 so functions outside of the dependencies are also calculated? The problem I have using Sendkeys Ctr/Alt/F9 (My.Computer.Keyboard.SendKeys("^%" & "{F9}", True))is the time delay before calculation is initiated (ca. 3 sec.) which I hoped to avoid with calculate.

thank you & regards

Michael
Posted 16 Jan, 2018 08:49:01 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Michael,

I suppose you are looking for declaring your function volatile: the function descriptor provides the IsVolatile property: set it to true. That is, put an ADXExcelFunctionCategory component onto the XLL module; in the visual designer, add a function descriptor to the category, choose the function (see the FunctionName property) and set IsVolatile=true.

A volatile function is updated whenever anything changes on the sheet:

- change B2 on your worksheet, this will call alera1 and alera2
- select B3 and press {F9}, this recalculates B4 as it refers to alera2 which is volatile


Andrei Smolin
Add-in Express Team Leader
Posted 16 Jan, 2018 10:26:36 Top
Michael Kaden


Guest


Dear Andrei,

thank you for the prompt reply. IsVolatile would only help me if I can set that property in code. My function alera2() with no parameters writes text to the cells and only has to be calculated if, for example the user changes the UI language. Most of the time these functions shall not be calculated. Language change will effect a change in resource and therefore the cells have to be filled with the new resource content.

Can the IsVolatile property = true/false be set by code?

thanks & kind regards

Michael
Posted 16 Jan, 2018 12:29:59 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Michael,

There's a way to do this from within the function itself. That is, the function must be called to let you execute Module.CallWorksheetFunction((ADXExcelWorksheetFunction)237, new object[] { false/true }) (C#); see also https://www.add-in-express.com/forum/read.php?FID=5&TID=9658.

Try to use Range.Dirty(); see https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/range-dirty-method-excel. You can also use a trigger argument; see https://www.add-in-express.com/forum/read.php?FID=5&TID=11651.

I've googled out a more comprehensive description of how Excel recalculates; see https://msdn.microsoft.com/en-us/library/bb687891(v=office.15).aspx.


Andrei Smolin
Add-in Express Team Leader
Posted 17 Jan, 2018 02:26:44 Top
Michael Kaden


Guest


Dear Andrei,

instead of using

My.Computer.Keyboard.SendKeys("^%" & "{F9}", True)

I use SendKeys.Send("^%" & "{F9}")

then the waiting time is eliminated and the functions are calculated without delay. This is what I wanted to achieve initially. I guess there is no reason to not use the SendKeys Class?

I use it from the ComAddIn

So for now the subject is solved.

Thank you & kind regards

Michael
Posted 19 Jan, 2018 06:26:39 Top

Posts 1 - 10 of 15
First | Prev. | 1 2 | Next | Last