• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 871
  • Last Modified:

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.Formula) Then
        MyFunction
    Else
        'Call the default action of the 'Paste function' button
        ActiveCell.FunctionWizard
    End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim ctl As CommandBarControl
    Set ctl = Application.CommandBars.FindControl(, 385)
    If Not ctl Is Nothing Then ctl.OnAction = ""
End Sub

Private Sub Workbook_Open()
    Dim ctl As CommandBarControl
    Set ctl = Application.CommandBars.FindControl(, 385)
    If Not ctl Is Nothing Then ctl.OnAction = "ThisWorkbook.PasteFunction"
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.FindControl(, 385)
End Sub

Private Sub cmdPasteFunction_Click(ByVal ctrl As CommandBarButton, CancelDefault As Boolean)
   If IsMyFunction(ActiveCell.Formula) Then
       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?
0
hor
Asked:
hor
  • 9
  • 6
1 Solution
 
criCommented:
Do not have the time now, but perhaps this can help:

http://www.experts-exchange.com/Applications/MS_Office/Q_20390478.html

And, BTW, is this a kind of wizard to help inputing the VLOOKUP function ? If yes, there is one by Microsoft.
0
 
horAuthor Commented:
Cri,
The hyperlink you gave is not the solution to the problem I encounter...
0
 
criCommented:
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.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
horAuthor Commented:
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?
0
 
criCommented:
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 ?

 
0
 
horAuthor Commented:
Yeah, I have my own toolbar button, and it works alright.
0
 
criCommented:
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.  
0
 
criCommented:
Try this, it seems to work on Excel 97 SR-2


Private Sub PasteFunction()
  With ActiveCell
    If .HasFormula And IsMyFunction(.Formula) Then
       MyFunction
    Else
      On Error Resume Next
         'Call dialog directly, as FunctionWizard method seems to be buggy in Excel 97
         Application.Dialogs(xlDialogFunctionWizard).Show
         If Err.Number <> 0 Then
            'Placeholder for error handler
            MsgBox "FunctionWizard called from " & .Address & " returned error number " & Err.Number
         End If
      On Error GoTo 0
    End If
  End With
End Sub
0
 
horAuthor Commented:
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?
0
 
criCommented:
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.
0
 
horAuthor Commented:
OK, most importantly the crash problem is solved now.
Thanks for the info.
0
 
criCommented:
ThAnk you. If you want, I can look into it, but it might take a few days.
0
 
horAuthor Commented:
Yes, I want it please.  You can take your time.
0
 
criCommented:
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("Worksheet Menu Bar").FindControl(Id:=385, Recursive:=True)
  If Not ctl Is Nothing Then ctl.OnAction = "ThisWorkbook.PasteFunction"
End Sub

Sub ResetInsertFunction()
  'Must be called separately, as FindControl returns only the first one
  Dim ctl As CommandBarControl
  Set ctl = Application.CommandBars("Worksheet Menu Bar").FindControl(Id:=385, Recursive:=True)
  If Not ctl Is Nothing Then ctl.OnAction = ""
 End Sub
 

0
 
criCommented:
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(strToolbarName).Delete
    On Error GoTo 0
    .CommandBars.Add(Name:=strToolbarName, Position:=msoBarTop).Visible = True
    With .CommandBars(strToolbarName).Controls
      .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 = xlHAlignCenterAcrossSelection
    Else
      .HorizontalAlignment = xlHAlignCenter
    End If
    .VerticalAlignment = xlVAlignCenter
  End With
 End Sub

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now