Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA as a programming language
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA as a programming language
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA as a programming language
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA as a programming language
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA as a programming language
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA as a programming language
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA as a programming language
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA as a programming language
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA as a programming language
On Feb 26, 2:23 am, JMB wrote:
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- Hide quoted text - - 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA as a programming language
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" wrote: On Feb 26, 2:23 am, JMB wrote: 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- Hide quoted text - - 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA as a programming language
On Mar 1, 3:03 am, JMB wrote:
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" wrote: On Feb 26, 2:23 am, JMB wrote: 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- Hide quoted text - - 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- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change German language data into Eglish Language in a colum | Excel Discussion (Misc queries) | |||
language support in excel sheet using a third party language tool | Excel Worksheet Functions | |||
Excel is written in which programming language ? | Excel Discussion (Misc queries) | |||
Which language will be suitable for excel programming | Excel Programming | |||
How to change the excel format from language to language? | Excel Discussion (Misc queries) |