?
Solved

Crash when using VLOOKUP in MS-Excel

Posted on 2003-03-12
15
Medium Priority
?
849 Views
Last Modified: 2012-06-21
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
Comment
Question by:hor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 13

Expert Comment

by:cri
ID: 8125466
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
 

Author Comment

by:hor
ID: 8133267
Cri,
The hyperlink you gave is not the solution to the problem I encounter...
0
 
LVL 13

Expert Comment

by:cri
ID: 8134833
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:hor
ID: 8135307
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
 
LVL 13

Expert Comment

by:cri
ID: 8141889
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
 

Author Comment

by:hor
ID: 8142746
Yeah, I have my own toolbar button, and it works alright.
0
 
LVL 13

Expert Comment

by:cri
ID: 8142771
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
 
LVL 13

Accepted Solution

by:
cri earned 2000 total points
ID: 8150184
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
 

Author Comment

by:hor
ID: 8150439
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
 
LVL 13

Expert Comment

by:cri
ID: 8151753
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
 

Author Comment

by:hor
ID: 8156169
OK, most importantly the crash problem is solved now.
Thanks for the info.
0
 
LVL 13

Expert Comment

by:cri
ID: 8156951
ThAnk you. If you want, I can look into it, but it might take a few days.
0
 

Author Comment

by:hor
ID: 8156979
Yes, I want it please.  You can take your time.
0
 
LVL 13

Expert Comment

by:cri
ID: 8189351
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
 
LVL 13

Expert Comment

by:cri
ID: 8189403
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question