Excel VBA as a programming language

[marco]

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

 

[JMB]

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

 

[JMB]

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]

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

 

[JMB]

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

 

[JMB]

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]

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









- 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

 

[JMB]

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

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

 

[marco]

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







- Show quoted text -

Hi again. Here it is (forgive me for the delay), and I have another
question!


Public Function gini()
gini = "." & Mid(Selection.Formula, 2)
End Function

Public Function gfind(name)
gfind = "." & Mid(Selection.Formula, 2)
End Function

Public Function execute_gini()
' do stuff here
End Function

Public Sub fun1()
Evaluate ("execute_" & Mid(Selection.Formula, 2))
End Sub

And on the workbook:

Private Sub Workbook_Open()
Application.OnKey "%{RIGHT}", "fun1"
Application.OnKey "%{DOWN}", "fun2"
Application.OnKey "%{LEFT}", "fun3"
End Sub

OK. The funny stuff is this: whenever I press ALT+(right arrow),
fun1 macro is called. If the cell has ".gini()" then "execute_gini()"
is called, but, the function runs twice. I don't know why. If I make
it a sub procedure instead of being a function, it runs twice too.
Why? What triggers the function twice?

I have read somewhere in this newsgroup that calling the function
without () should resolve the problem (no explanations given), but I
have to call it with () because it contains arguments (not .gini()
itself, but other functions using the same mechanism).

Any advice would be appreciated, and thank you in advance,
Marco Ferra

 

[JMB]

It doesn't run twice on my machine. It looks like the execute macro(s) are
doing all of the work. Perhaps set up a test (below), and step through the
code and see what line is causing the function to calculate twice (F8 key).

Public Sub Test()
x = execute_gini
End Sub

Overall, it seems a little awkward to me. Does the execute macro hardcode
the values? If so, another approach you might consider is declaring a public
range variable and have "fun1" set the range to whatever range you want to
calculate. Then do a find/replace on the range you want recalculated, which
will trigger the functions within that range to recalculate. But, include
code in each function to test if it is within the range set by "fun1". If
not, return the formula. If so, calculate a value.

If you are not hardcoding the values into the cells, then if something else
causes the functions to recalculate (such as changing a cell that is used as
one of their arguments) - they will revert back to showing a formula and not
a calculated value.



Public rngCalculate As Range

Public Function gini()
If rngCalculate Is Nothing Then
gini = "." & Mid(Application.Caller.Formula, 2)
ElseIf Intersect(Application.Caller, rngCalculate) Is Nothing Then
gini = "." & Mid(Application.Caller.Formula, 2)
Else: gini = 5
End If
End Function

Public Function gfind(name)
If rngCalculate Is Nothing Then
gfind = "." & Mid(Application.Caller.Formula, 2)
ElseIf Intersect(Application.Caller, rngCalculate) Is Nothing Then
gfind = "." & Mid(Application.Caller.Formula, 2)
Else: gfind = "Test"
End If
End Function

Public Sub fun1()
Set rngCalculate = Selection
With Selection
.Replace what:="=", _
replacement:="=", _
lookat:=xlPart, _
MatchCase:=False, _
matchbyte:=False
End With
Set rngCalculate = Nothing
Selection.Value = Selection.Value
End Sub

 

[marco]

It doesn't run twice on my machine. It looks like the execute macro(s) are
doing all of the work. Perhaps set up a test (below), and step through the
code and see what line is causing the function to calculate twice (F8 key).

Public Sub Test()
x = execute_gini
End Sub

Overall, it seems a little awkward to me. Does the execute macro hardcode
the values? If so, another approach you might consider is declaring a public
range variable and have "fun1" set the range to whatever range you want to
calculate. Then do a find/replace on the range you want recalculated, which
will trigger the functions within that range to recalculate. But, include
code in each function to test if it is within the range set by "fun1". If
not, return the formula. If so, calculate a value.

If you are not hardcoding the values into the cells, then if something else
causes the functions to recalculate (such as changing a cell that is used as
one of their arguments) - they will revert back to showing a formula and not
a calculated value.

Public rngCalculate As Range

Public Function gini()
If rngCalculate Is Nothing Then
gini = "." & Mid(Application.Caller.Formula, 2)
ElseIf Intersect(Application.Caller, rngCalculate) Is Nothing Then
gini = "." & Mid(Application.Caller.Formula, 2)
Else: gini = 5
End If
End Function

Public Function gfind(name)
If rngCalculate Is Nothing Then
gfind = "." & Mid(Application.Caller.Formula, 2)
ElseIf Intersect(Application.Caller, rngCalculate) Is Nothing Then
gfind = "." & Mid(Application.Caller.Formula, 2)
Else: gfind = "Test"
End If
End Function

Public Sub fun1()
Set rngCalculate = Selection
With Selection
.Replace what:="=", _
replacement:="=", _
lookat:=xlPart, _
MatchCase:=False, _
matchbyte:=False
End With
Set rngCalculate = Nothing
Selection.Value = Selection.Value
End Sub















- Show quoted text -

I understand your code, and I have found what triggers the function
twice; execute_gini() doesn't have any arguments, and if it called
just like that, it runs twice. Calling by execute_gini (without the
parentheses), runs only once. I don't have an explanation for this
behaviour.

Thanks a lot for your ideas and quick responses,
Marco