hor
asked on
Crash when using VLOOKUP in MS-Excel
I do the following in MS-Excel:
1) Create an Excel add-in (.xla) file. I named it testing.xla.
2) In the 'ThisWorkbook' of this file, copy and paste the following VBA codes:
Private Sub PasteFunction()
If Not ActiveCell.HasFormula Then
'Call the default action of the 'Paste function' button
ActiveCell.FunctionWizard
ElseIf IsMyFunction(ActiveCell.Fo
MyFunction
Else
'Call the default action of the 'Paste function' button
ActiveCell.FunctionWizard
End If
End Sub
Private Sub Workbook_BeforeClose(Cance
Dim ctl As CommandBarControl
Set ctl = Application.CommandBars.Fi
If Not ctl Is Nothing Then ctl.OnAction = ""
End Sub
Private Sub Workbook_Open()
Dim ctl As CommandBarControl
Set ctl = Application.CommandBars.Fi
If Not ctl Is Nothing Then ctl.OnAction = "ThisWorkbook.PasteFunctio
End Sub
Private Sub MyFunction()
MsgBox "MyFunction"
End Sub
Private Function IsMyFunction(ByVal sFormula As String) As Boolean
IsMyFunction = True
End Function
3) Basically the above codes do the following things:
- find the button 'Paste function' in MS-Excel (which has id = 385) by using the method FindControl.
- then change the default action of the button by using the property OnAction, so that it will call the customized function PasteFunction instead of its own default action.
- in the function PasteFunction, it is checking whether the active cell contains a customized formula. If it does not, then call the default action of the 'Paste function' button.
4) Now after I've installed this add-in file by selecting it in the menu 'Tools, Add-ins', I create a new workbook in MS-Excel.
5) Click on any cell in the workbook. Then click on the button 'Paste function' in the toolbar. Select the function 'VLOOKUP'.
6) In the field 'Lookup_value', click on the button on the right of the field to select a range. Then click on cell A1 in Sheet 1. Notice the field now contains 'Sheet1!A1'.
7) For the next field 'Table_array', click on the button on the right of the field to select a range. This time click on cell A1 in Sheet 2. Notice the field now contains 'Sheet2!A1'.
8) Now click on OK button. MS-Excel crash.
It happens in both Office 97 and Office 2000.
In Office 2000, I can solve it by using the following method instead of the onAction property:
Public WithEvents cmdPasteFunction As CommandBarButton
Private Sub Workbook_Open()
Set cmdPasteFunction = Application.CommandBars.Fi
End Sub
Private Sub cmdPasteFunction_Click(ByV
If IsMyFunction(ActiveCell.Fo
CancelDefault = True
MyFunction
End If
End Sub
But this statement is not supported in Office 97.
Public WithEvents cmdPasteFunction As CommandBarButton
Does anyone know how to solve this in Office 97?
Another question is: how do I make the menu 'Insert, Function...' in MS-Excel to call my own customized function in VBA?
1) Create an Excel add-in (.xla) file. I named it testing.xla.
2) In the 'ThisWorkbook' of this file, copy and paste the following VBA codes:
Private Sub PasteFunction()
If Not ActiveCell.HasFormula Then
'Call the default action of the 'Paste function' button
ActiveCell.FunctionWizard
ElseIf IsMyFunction(ActiveCell.Fo
MyFunction
Else
'Call the default action of the 'Paste function' button
ActiveCell.FunctionWizard
End If
End Sub
Private Sub Workbook_BeforeClose(Cance
Dim ctl As CommandBarControl
Set ctl = Application.CommandBars.Fi
If Not ctl Is Nothing Then ctl.OnAction = ""
End Sub
Private Sub Workbook_Open()
Dim ctl As CommandBarControl
Set ctl = Application.CommandBars.Fi
If Not ctl Is Nothing Then ctl.OnAction = "ThisWorkbook.PasteFunctio
End Sub
Private Sub MyFunction()
MsgBox "MyFunction"
End Sub
Private Function IsMyFunction(ByVal sFormula As String) As Boolean
IsMyFunction = True
End Function
3) Basically the above codes do the following things:
- find the button 'Paste function' in MS-Excel (which has id = 385) by using the method FindControl.
- then change the default action of the button by using the property OnAction, so that it will call the customized function PasteFunction instead of its own default action.
- in the function PasteFunction, it is checking whether the active cell contains a customized formula. If it does not, then call the default action of the 'Paste function' button.
4) Now after I've installed this add-in file by selecting it in the menu 'Tools, Add-ins', I create a new workbook in MS-Excel.
5) Click on any cell in the workbook. Then click on the button 'Paste function' in the toolbar. Select the function 'VLOOKUP'.
6) In the field 'Lookup_value', click on the button on the right of the field to select a range. Then click on cell A1 in Sheet 1. Notice the field now contains 'Sheet1!A1'.
7) For the next field 'Table_array', click on the button on the right of the field to select a range. This time click on cell A1 in Sheet 2. Notice the field now contains 'Sheet2!A1'.
8) Now click on OK button. MS-Excel crash.
It happens in both Office 97 and Office 2000.
In Office 2000, I can solve it by using the following method instead of the onAction property:
Public WithEvents cmdPasteFunction As CommandBarButton
Private Sub Workbook_Open()
Set cmdPasteFunction = Application.CommandBars.Fi
End Sub
Private Sub cmdPasteFunction_Click(ByV
If IsMyFunction(ActiveCell.Fo
CancelDefault = True
MyFunction
End If
End Sub
But this statement is not supported in Office 97.
Public WithEvents cmdPasteFunction As CommandBarButton
Does anyone know how to solve this in Office 97?
Another question is: how do I make the menu 'Insert, Function...' in MS-Excel to call my own customized function in VBA?
ASKER
Cri,
The hyperlink you gave is not the solution to the problem I encounter...
The hyperlink you gave is not the solution to the problem I encounter...
The link shows how to use the WithEvent object. I thought you could use it.
As for your problem.
I loaded your code. The first time it crashed Excel (97, SR-2). Then I tried it again, this time filling all the VLOOKUP fields, and the placeholder "MyFunction" message was displayed. So far so good. I thought it could be that the 'not enough parameters' message which Excel would return is interfering with your code somehow. BUT then I tried again according your procedure, and now it runs. Go figure. Microsoft. I doubt you will be able to fix this.
If you state what you actually are trying to implement, then perhaps I can suggest a work around.
BTW: Insert|Function is not yet covered.
As for your problem.
I loaded your code. The first time it crashed Excel (97, SR-2). Then I tried it again, this time filling all the VLOOKUP fields, and the placeholder "MyFunction" message was displayed. So far so good. I thought it could be that the 'not enough parameters' message which Excel would return is interfering with your code somehow. BUT then I tried again according your procedure, and now it runs. Go figure. Microsoft. I doubt you will be able to fix this.
If you state what you actually are trying to implement, then perhaps I can suggest a work around.
BTW: Insert|Function is not yet covered.
ASKER
In Office 97, the WithEvents object of the CommandBarButton is not supported.
The Excel will crash particularly when you switch between two sheets or more.
For example, in the field 'Lookup_value', you select 'Sheet1!A1', and in the field 'Table_array', you select 'Sheet2!A1', and even if I fill up all the VLOOKUP fields, Excel will still crash.
What I want to implement is to catch the click event of the 'Paste function' button. So that I can check in the event whether the active cell contains my own customized function. If it does, then I want to call my own input form, instead of the 'Paste function' form, and vice versa.
When I use the property OnAction to catch the click event, Excel crash when I use the VLOOKUP function.
So, I want to know if there is other way of catching the click event in Office 97 that will not affect the use of VLOOKUP function?
The Excel will crash particularly when you switch between two sheets or more.
For example, in the field 'Lookup_value', you select 'Sheet1!A1', and in the field 'Table_array', you select 'Sheet2!A1', and even if I fill up all the VLOOKUP fields, Excel will still crash.
What I want to implement is to catch the click event of the 'Paste function' button. So that I can check in the event whether the active cell contains my own customized function. If it does, then I want to call my own input form, instead of the 'Paste function' form, and vice versa.
When I use the property OnAction to catch the click event, Excel crash when I use the VLOOKUP function.
So, I want to know if there is other way of catching the click event in Office 97 that will not affect the use of VLOOKUP function?
I thought I had found an additional cause, which would be the VLOOKUP formula returning #N/A (which you get if you pass a one cell array like in your example). But, _sometimes_ it also crashes with a valid formula. Then I thought to have managed to get it running by not using the 'ElseIf' in the Sub 'PasteFunction', but again, sometimes it runs, sometimes not. These intermittent errors are pesky to troubleshoot/circumvent.
Will look into alternatives.
BTW: Did you already test whether your setup would work when called from an own toolbar button ?
Will look into alternatives.
BTW: Did you already test whether your setup would work when called from an own toolbar button ?
ASKER
Yeah, I have my own toolbar button, and it works alright.
Not sure whether there is not a misunderstanding: Does the code above work on a Excel 97 if you call it from a custom toolbar ?
Personally I do not advocate customizing any standard menus, because users will be lost/irritated when using an not customized Excel. Adding a toolbar on the fly is _my_ solution.
Personally I do not advocate customizing any standard menus, because users will be lost/irritated when using an not customized Excel. Adding a toolbar on the fly is _my_ solution.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ah, it works! Thanks!
Now the other question is: do you know how I can make the menu 'Insert, Function...' in MS-Excel to call my own customized function in VBA?
Now the other question is: do you know how I can make the menu 'Insert, Function...' in MS-Excel to call my own customized function in VBA?
I'd say the analog way as for the button, but do not loose too much time with it because, as explained above, I never done it.
For the ID download the Commandbar Tools of http://www.erlandsendata.no/english/downloads/commandbars.php
There are also tons of examples, this site is worth to be bookmarked.
For the ID download the Commandbar Tools of http://www.erlandsendata.no/english/downloads/commandbars.php
There are also tons of examples, this site is worth to be bookmarked.
ASKER
OK, most importantly the crash problem is solved now.
Thanks for the info.
Thanks for the info.
ThAnk you. If you want, I can look into it, but it might take a few days.
ASKER
Yes, I want it please. You can take your time.
Test if you can use/integrate this:
Sub ModInsertFunction()
'Must be called separately, as FindControl returns only the first one
Dim ctl As CommandBarControl
Set ctl = Application.CommandBars("W
If Not ctl Is Nothing Then ctl.OnAction = "ThisWorkbook.PasteFunctio
End Sub
Sub ResetInsertFunction()
'Must be called separately, as FindControl returns only the first one
Dim ctl As CommandBarControl
Set ctl = Application.CommandBars("W
If Not ctl Is Nothing Then ctl.OnAction = ""
End Sub
Sub ModInsertFunction()
'Must be called separately, as FindControl returns only the first one
Dim ctl As CommandBarControl
Set ctl = Application.CommandBars("W
If Not ctl Is Nothing Then ctl.OnAction = "ThisWorkbook.PasteFunctio
End Sub
Sub ResetInsertFunction()
'Must be called separately, as FindControl returns only the first one
Dim ctl As CommandBarControl
Set ctl = Application.CommandBars("W
If Not ctl Is Nothing Then ctl.OnAction = ""
End Sub
Can this guy not give up dept...
This code makes you a nice toolbar
Sub MustHaveToolbar()
'Small toolbar with 8+1 handy buttons otherwise buried too deep
'Needs auxiliary Sub auxCenterAcrossSelection
Const strToolbarName As String = "myMustHaveBar"
With Application
On Error Resume Next
.CommandBars(strToolbarNam
On Error GoTo 0
.CommandBars.Add(Name:=str
With .CommandBars(strToolbarNam
.Add Type:=msoControlButton
With .Item(1)
.OnAction = "auxCenterAcrossSelection"
.Caption = "Center Across Selection (Do NOT use Merge Cells !)"
.FaceId = 386 '1 Generic Button with Justify FaceId
End With
.Add Type:=msoControlButton, Id:=225 '2 Lock
.Item(2).BeginGroup = True
.Add Type:=msoControlButton, Id:=486 '3 Trace Precedents
.Item(3).BeginGroup = True
.Add Type:=msoControlButton, Id:=453 '4 Remove Arrows
.Add Type:=msoControlButton, Id:=451 '5 Trace Dependents
.Add Type:=msoControlButton, Id:=3160 '6 Ungroup
.Item(6).BeginGroup = True
.Add Type:=msoControlButton, Id:=440 '7 Show/Hide Outline
.Add Type:=msoControlButton, Id:=3159 '8 Group
.Add Type:=msoControlButton
With .Item(9)
.BeginGroup = True
.OnAction = "PasteFunction"
.Caption = "MyVerySpecialFunction"
.FaceId = 385 '9 Magic button
End With
End With
End With
End Sub
Remarks:
a) PasteFunction and all code other than Open/Close events should be placed in a module i.e. not in ThisWorkbook level
b) If you like my toolbar, here
Sub auxCenterAcrossSelection()
'The MergeSelection icon should not be used aligning titles
'Needed in MustHaveToolbar macro
With Selection
If .Columns.Count > 1 Then
.MergeCells = False
.WrapText = False
.ShrinkToFit = False
.HorizontalAlignment = xlHAlignCenterAcrossSelect
Else
.HorizontalAlignment = xlHAlignCenter
End If
.VerticalAlignment = xlVAlignCenter
End With
End Sub
This code makes you a nice toolbar
Sub MustHaveToolbar()
'Small toolbar with 8+1 handy buttons otherwise buried too deep
'Needs auxiliary Sub auxCenterAcrossSelection
Const strToolbarName As String = "myMustHaveBar"
With Application
On Error Resume Next
.CommandBars(strToolbarNam
On Error GoTo 0
.CommandBars.Add(Name:=str
With .CommandBars(strToolbarNam
.Add Type:=msoControlButton
With .Item(1)
.OnAction = "auxCenterAcrossSelection"
.Caption = "Center Across Selection (Do NOT use Merge Cells !)"
.FaceId = 386 '1 Generic Button with Justify FaceId
End With
.Add Type:=msoControlButton, Id:=225 '2 Lock
.Item(2).BeginGroup = True
.Add Type:=msoControlButton, Id:=486 '3 Trace Precedents
.Item(3).BeginGroup = True
.Add Type:=msoControlButton, Id:=453 '4 Remove Arrows
.Add Type:=msoControlButton, Id:=451 '5 Trace Dependents
.Add Type:=msoControlButton, Id:=3160 '6 Ungroup
.Item(6).BeginGroup = True
.Add Type:=msoControlButton, Id:=440 '7 Show/Hide Outline
.Add Type:=msoControlButton, Id:=3159 '8 Group
.Add Type:=msoControlButton
With .Item(9)
.BeginGroup = True
.OnAction = "PasteFunction"
.Caption = "MyVerySpecialFunction"
.FaceId = 385 '9 Magic button
End With
End With
End With
End Sub
Remarks:
a) PasteFunction and all code other than Open/Close events should be placed in a module i.e. not in ThisWorkbook level
b) If you like my toolbar, here
Sub auxCenterAcrossSelection()
'The MergeSelection icon should not be used aligning titles
'Needed in MustHaveToolbar macro
With Selection
If .Columns.Count > 1 Then
.MergeCells = False
.WrapText = False
.ShrinkToFit = False
.HorizontalAlignment = xlHAlignCenterAcrossSelect
Else
.HorizontalAlignment = xlHAlignCenter
End If
.VerticalAlignment = xlVAlignCenter
End With
End Sub
https://www.experts-exchange.com/questions/20390478/Excel-97-SR-2-Temporary-Interception-of-Print-Command.html
And, BTW, is this a kind of wizard to help inputing the VLOOKUP function ? If yes, there is one by Microsoft.