Visual Basic Editor Autocomplete Crash

[AshwinKumar]

Within the Excel 2007 Visual Basic Editor, when I declare a variable as a Worksheet object and then, within a subroutine, set that variable to point to a worksheet object, if I start typing the code 'ws.', Excel crashes.

Normally upon typing the . after ws, I would expect the Visual Basic Editor's autocomplete function to bring up from the object model a drop down list of all of the members (methods, properties, etc.) of the worksheet object for me to choose from. However this does not happen and Excel crashes instead.

Other uses of the Visual Basic Editor's autocomplete function (e.g. where a variable is declared as and assigned to a range) do not prompt a crash - I've only noticed it with variables assigned to worksheet objects. The problem has been happening for the last few weeks.

My workaround is to use alternative text in place of ws (e.g. 'x.Cells(1,1).Clear') and then later use a find and replace to put ws back into my code at the relevant points but whenever I forget to do this whilst I'm in the middle of typing code, Excel crashes and I have to restart and go back to back to my last save - very frustrating!

I'm running Excel 2007 (12.0.6665.5003) SP3 MSO (12.0.6662.5000).

Has anyone else experienced this problem and can anyone offer a solution.

Many thanks for any help at all.

Dim ws As Worksheet
Dim rng As Range

Sub ExampleSub()

    Set ws = ActiveSheet
    Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(1, 1))
    MsgBox rng.Rows.Count
    ws.

End Sub

 

[Michael M]

Your attached code works fine...but are there other macros involved here....if not try enclosing the dims inside the sub rather than outside.
and I'd use differnt nmes for the which DONT include the word SUb...anything to reduce confusion.

 

[AshwinKumar]

Hi Michael

Thanks for replying. The names of the subroutines aren't an issue because the problem has occurred whilst editing a number of subroutines. Also, the code itself isn't the problem, it's that the Visual Basic Editor will crash when I type 'ws.'

My workaround enables me to generate the code I need (e.g. ws.Cells...) which I can test and run in the normal way but typing ws. will crash the editor and Excel.

Hope that's clear

Ashwin

 

[Michael M]

As mentioned, there must be something triggering an action when "ws." is used...that's why I asked about other macros.
Also, I wasn't meaning the Sub name was going to be the problem, more of a housekeeping issue, to use more "appropriate" names for subs
You could also check for addins running in the background, that use the "ws."
Otherwise, I've never seen this happen before.

 

[AshwinKumar]

Hi Michael

Thanks for your time. The only action that I can think of that is being triggered is the lookup to the Excel object model which populates the autocomplete drop-down list. However, I've no idea why the problem is specific to the worksheet object rather than others in the object model. Hopefully someone will have come across this before!

Take care

Ashwin