On Feb 23, 10:52 pm, JMB wrote:
I don't know how or if you can keep excel from calculating the function when
you enter =gini() and hit enter unless the cell is preformatted as text (but
then the cell will actually display =gini()).

The only other thing I can think of is to change the numberformat to display
"gini()" when the formula is entered then use the Alt+Arrow keys to change
the numberformat to General to display the calculated value.

Put this in the Thisworkbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rngCell As Range
Dim strTemp As String

On Error GoTo ExitSub
Application.EnableEvents = False

For Each rngCell In Target.Cells
With rngCell
strTemp = Replace(.Formula, "=", ".", 1, 1, vbTextCompare)
strTemp = Replace(strTemp, """", """""", 1, -1, vbTextCompare)
strTemp = """" & strTemp & """"
If .Formula Like "=gini(*)" Or _
.Formula Like "=gfind(*)" Or _
.Formula Like "=gout(*)" Then
.NumberFormat = strTemp & ";" & strTemp & ";" _
& strTemp & ";" & strTemp
End If
End With
Next rngCell

ExitSub:
Application.EnableEvents = True

End Sub

Put this in the Thisworkbook module

Private Sub Workbook_Open()
Application.OnKey "%{LEFT}", "Macro2"
Application.OnKey "%{DOWN}", "Macro3"
End Sub

and these in a general module

Sub Macro2()
With ActiveCell
If Left(.Text, 1) = "." Then _
.NumberFormat = "General"
End With
End Sub

Sub Macro3()
Dim rngCell As Range

For Each rngCell In Range(ActiveCell, _
ActiveCell.End(xlDown)).Cells
With rngCell
If Left(.Text, 1) = "." Then _
.NumberFormat = "General"
End With
Next rngCell
End Sub



"marco" wrote:
On Feb 23, 2:19 am, JMB wrote:
Just to get you started, you could put code like this in the Thisworkbook
module, which will run Macro2 when you hit Alt-Left Arrow and Macro3 when you
hit Alt-Down Arrow.

Private Sub Workbook_Open()
Application.OnKey "%{LEFT}", "Macro2"
Application.OnKey "%{DOWN}", "Macro3"
End Sub

I would actually input the function in the cell with the period (ie
".gini()") and have Macro2 and Macro3 (which are in a generic module) test
the first character for a period and replace it with an = sign.

Sub Macro2()
With ActiveCell
If Left(.Text, 1) = "." Then _
.Formula = "=" & Right(.Formula, _
Len(.Formula) - 1)
End With
End Sub

Sub Macro3()
Dim rngCell As Range

For Each rngCell In Range(ActiveCell, _
ActiveCell.End(xlDown)).Cells
With rngCell
If Left(.Text, 1) = "." Then _
.Formula = "=" & Right(.Formula, _
Len(.Formula) - 1)
End With
Next rngCell
End Sub

You may need to change the range you want Macro3 to run on (I just have it
going until there is a break in the data - did you intend the entire column
below the activecell?).

Yes, it worked, thank you, but I didn't quite understand the
"ActiveCell.End(xlDown)).Cells". What is the xlDown ? A special
argument?

Unfortunally, I cannot put .gini() because the function, on the cell,
should really work like a function. .gini() is just a representation
to see that is code, and when entering =gini() and striking the ENTER
key, the function should not run.

If anyone of you can help me, I would be most appreciated.
Nevertheless, thanks JMB for the help, it worked.

Sincere regards,
Marco Ferra- Hide quoted text -

- Show quoted text -

Excellent, I understand your idea and I will test it on Monday when I
arrive to work. I'll also post here the code that I have developed
(it doesn't work completely, but almost does!) for you to see. Your
advice was excellent, thank you very much.

Sincere regards,
Marco