Hi group, this is my first time here. I'll get right away to the
point.

I'm trying to re-write a piece of software on Excel using the builted-
in Visual Basic engine (VBA) in order to communicate to GPIB devices
(instruments like spectrum analyzers and power meters). I'm trying to
create my own programming language on Excel; I'll explain.

On each cell, I should be able to write:

A1 =gini()
A2 =gfind(abc)
A3 =gout(abc, "GPIB command", B3)

=g* are all functions, and none of them return nothing.

These functions should NOT run when I enter them on any cells (on
pressing the ENTER key). Instead, it should not appear 0 (because the
function was executed and returned 0), but the function name like, for
example, .gini() and .gfind(abc) (note the dot). This way, I can
see the commands on the worksheet, instead of the result, and the
functions are not runned.

Then, on the current ActiveCell, if I press ALT+(left arrow), only the
function on that cell should run, and if I pressed ALT+(down arrow),
it should run all commands on all cells below.

Note, on the function bar I can see =gini(), but on the cell itself I
should see .gini(). Pressing ALT+(left arrow), =gini() (the gini()
function) should run.

I'm over this problem for almost a week, and I feel frustrated with my
results. Can anyone point me good directions on how to solve this
efficiently?

Best regards, and thank to you all in advance for reading,
Marco Ferra

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?).


"marco" wrote:

Hi group, this is my first time here. I'll get right away to the
point.

I'm trying to re-write a piece of software on Excel using the builted-
in Visual Basic engine (VBA) in order to communicate to GPIB devices
(instruments like spectrum analyzers and power meters). I'm trying to
create my own programming language on Excel; I'll explain.

On each cell, I should be able to write:

A1 =gini()
A2 =gfind(abc)
A3 =gout(abc, "GPIB command", B3)

=g* are all functions, and none of them return nothing.

These functions should NOT run when I enter them on any cells (on
pressing the ENTER key). Instead, it should not appear 0 (because the
function was executed and returned 0), but the function name like, for
example, .gini() and .gfind(abc) (note the dot). This way, I can
see the commands on the worksheet, instead of the result, and the
functions are not runned.

Then, on the current ActiveCell, if I press ALT+(left arrow), only the
function on that cell should run, and if I pressed ALT+(down arrow),
it should run all commands on all cells below.

Note, on the function bar I can see =gini(), but on the cell itself I
should see .gini(). Pressing ALT+(left arrow), =gini() (the gini()
function) should run.

I'm over this problem for almost a week, and I feel frustrated with my
results. Can anyone point me good directions on how to solve this
efficiently?

Best regards, and thank to you all in advance for reading,
Marco Ferra

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?).


"marco" wrote:

Hi group, this is my first time here. I'll get right away to the
point.

I'm trying to re-write a piece of software on Excel using the builted-
in Visual Basic engine (VBA) in order to communicate to GPIB devices
(instruments like spectrum analyzers and power meters). I'm trying to
create my own programming language on Excel; I'll explain.

On each cell, I should be able to write:

A1 =gini()
A2 =gfind(abc)
A3 =gout(abc, "GPIB command", B3)

=g* are all functions, and none of them return nothing.

These functions should NOT run when I enter them on any cells (on
pressing the ENTER key). Instead, it should not appear 0 (because the
function was executed and returned 0), but the function name like, for
example, .gini() and .gfind(abc) (note the dot). This way, I can
see the commands on the worksheet, instead of the result, and the
functions are not runned.

Then, on the current ActiveCell, if I press ALT+(left arrow), only the
function on that cell should run, and if I pressed ALT+(down arrow),
it should run all commands on all cells below.

Note, on the function bar I can see =gini(), but on the cell itself I
should see .gini(). Pressing ALT+(left arrow), =gini() (the gini()
function) should run.

I'm over this problem for almost a week, and I feel frustrated with my
results. Can anyone point me good directions on how to solve this
efficiently?

Best regards, and thank to you all in advance for reading,
Marco Ferra

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

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

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

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

You're welcome. Good luck w/your project.

"marco" wrote:

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