2

I have been following this Tutorial to create an excel add-in in C#. This add-in gives the user a set of functions that they write into the formula bar.

Example function

For some reason this tutorial works and the cell is populated when the VBA Editor runs the Sub (Through f5), or if an Excel Form Button which is bound to the VBA Sub is clicked.

However, if the Sub is called from a function (which can be used in the formula bar), either an error (A fairly catch-all error that seems to be a 'Something happened' error) appears down in the c# code, or the cell is not populated.

Public Function methodCaller()
    Call CallVSTOMethod 'Call the sub
End Function

AFAIK Call doesn't do anything special, its meant to be a hangover from earlier versions of VBA.

My question is, how can I call a Sub from a function in a similar manner to the editor/ form click handler?

Thank you for your time.

EDIT: This is the error when i call the Sub from a function

Error

Community
  • 1
  • 1
PiMan
  • 55
  • 10
  • 3
    `how can I call a Sub from a function, without the Call keyword` - delete the `Call` keyword. Are you trying to [change the worksheet from your functions](http://stackoverflow.com/q/23433096/11683) by any chance? – GSerg Aug 17 '15 at 14:43
  • One of those rare instances where the answer is in the question. – Bond Aug 17 '15 at 14:44
  • Same error happens, apologies, I'll ammend my question – PiMan Aug 17 '15 at 14:46
  • Yes, you are trying to change worksheets from functions called from a worksheet. Please don't. Do see the link, but please don't do it. – GSerg Aug 17 '15 at 14:51
  • The point of these formulae is that they have total control of the worksheet. You write one into the worksheet with your parameters and it populates everything else from a Database or other Persistence Mechanism. – PiMan Aug 17 '15 at 14:56
  • This is not how Excel works. If you want this kind of thing, you need to create a procedure that does it and provide the user with means to call it, but must not put the call in a sheet formula (have a button on the sheet instead, or a button on the toolbar, or document how to call your routine from user code). You can try forcing your thing to happen, but it's so much against how Excel is designed that it should and I believe will break. – GSerg Aug 17 '15 at 15:01
  • That seems to be the point we were reaching here too. We were simply wondering if there was a way to do the job with a formula bar input. Thank you all for your help. If I may ask, why is this not 'The Excel way'? It seems I do not understand why this doesn't work. – PiMan Aug 17 '15 at 15:06
  • 1
    You need to ask Excel developers for that, I can only speculate. The calculation cycle is optimized, a function is only recalculated if its arguments change, so Excel keeps track of dependencies. Allowing worksheet functions to change sheet breaks this in two ways - no more able to properly track dependencies, so the calculation tree collapses, and the possibility of infinite calculation cycles. – GSerg Aug 17 '15 at 15:12
  • I see now. Thank you very much GSerg :). If you put your answer below, I will mark it as the correct one. – PiMan Aug 17 '15 at 15:42

0 Answers0