The difference between the enter key and .Calculate (perhaps Evaluate?)

  • Thread starter news.microsoft.com
  • Start date
N

news.microsoft.com

Hi microsoft.public.excel.programming newsgroup, this is my first time here.

I'm developing an event driving application in Excel, mostly controlled by
the keyboard, namely ALT+(cursor keys), and I have this function on Module1
of a .xls:

Public Function gini()
MsgBox "do instructions here"
gini = Replace(Application.Caller.Formula, "=", ".")
End Function

and on ThisWorkbook I have the following event:

Public Sub Workbook_Open()
Application.OnKey "%{RIGHT}", "parseOnce"
Application.OnKey "%{DOWN}", "parseForever"
End Sub

the parseOnce() is as follows:

Public Function parseOnce(Optional cell As Range)
Set iPtr = IIf(cell Is Nothing, ActiveCell, cell)
hasParsed = False

If iPtr.HasFormula = True Then
iPtr.Calculate
hasParsed = True
End If

parseOnce = hasParsed
End Function

iPtr (shorthand for instruction pointer) is a Range type variable either
containing the ActiveCell of the cell passed as an argument to parseOnce().
Issuing iPtr.Calculate does exactly what I want, it displays the message "do
instructions here". However, pressing Enter after entering the =gini()
formula does the same thing, and that I don't want.

My question is, how can I execute (or calculate?) a formula/function on
pressing ALT+(right-arrow), without executing it when it is entered on the
cell ? When using Excel's shortcut Alt+Shift+Ctrl+F9 (Re-calculate all),
any of these functions shouldn't run either, they should only run with the
ALT+key combination. Any other Excel formula, for example =SUM() should run
when entered, or re-calculated.

I hope you can help me and thank you for reading so far, sincere regards,
Marco
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top