VBE - How do I clear the auto-correct?

[JayCarr]

I accidentally named one of my variables "count" (I'm new, give me a break.) As a result every time I try and use the Count property VBE autocorrects it to be "count" instead.

For example

rowCount = Selection.Rows.Count

Will be auto corrected to read:

rowCount = Selection.Rows.count

The second example will not return a darn thing. But no matter how many times I capitalize the "c" to a "C" it will be auto corrected back to a lowercase "c".

How do I clear out the autocorrector?

Many thanks.

 

[Joe4]

Welcome to the Board!

VBA cannot/will not distinguish between "Count" and "count". You will need to change the name of your "count" variable to something else (i.e., "mycount").

As you can see, using reserved words for variables in VBA can cause major problems. Once you change the variable name (and all references to the variable in your code), things should be fine.

 

[JayCarr]

Sadly that doesn't appear to work. I renamed all of my variables to something like "myCount" or "theCount" or what have you. Still the lower case "count"s remain. I even marked all of the offending properties as comments, restarted the worksheet, made sure there was not even an opportunity for the variable "count" to exist, upper cased all of my properties before uncommenting them and then, after the uncomment, they all turned right back into lower case letters.

Option Explicit

Sub Duplicate()

    Dim rowCount As Integer
    Dim myCount As Integer
    
       
    Sheet1.UsedRange.Select
    Selection.Offset(2, 0).Select
    Selection.Resize(Selection.Rows.count - 2).Select
    rowCount = Selection.Rows.count
    AddNumbers (rowCount)
    
    
    
End Sub

Sub AddNumbers(theCount As Integer)
        
        Dim i As Integer
        
        Do While i < theCount
            i = i + 1
            
            Dim temp As Range
            temp = Cells(Rows.count, "A").End(xlUp).Row
            temp.Value = 2
        Loop
        
        
        
End Sub

Every single place that I had *.count I did what I listed above. Perhaps there is something I am missing? Can you (hopefully) see something I can't? I mean, it wouldn't be the end of the world to just retype this, but I'd like to know how to fix this problem just incase I have a similar screw up in a much larger file.

Oh, and I should probably mention I'm using Excel 2007 on Windows 7. Not sure if it makes a difference, but there you go.

 

[Norie]

Have you saved and closed the workbook you are working in then closed Excel down and re-opened?

I've had this problem in the past and I think that's how I dealt with it.

Might have compiled the code too.

 

[JayCarr]

I've shut down Excel, but I haven't tried compiling it yet. I'll give it a shot.

Edit: Nope.

I literally deleted every instance of "count" from the workbook, saved it, opened it, compiled it. Still, every time I write something like "Rows.Count" it autocorrects to "Rows.count".

Is there no way to just turn Auto-correct off? I can't find that option anywhere either... I find it hard to believe that if someone where to make this simple mistake on a large Macro they'd have to simply start over.

Oh, maybe I'll try deleting all counts, copying the whole thing to the clipboard, deleting the macro, saving, opening, compiling, creating a new Macro and pasting. That should clear it out...I should think.

 

[JayCarr]

Success!

So anyone else facing this problem, here's what I did:

1) Delete all references to the variable (either the variable itself or the reserved name that is being auto corrected to the variable name.)
2) Copy the whole module to the clipboard.
3) Remove the module.
4) Save the workbook and close Excel completely.
5) Open Excel, insert a new module into your workbook.
6) Paste the clipboard into your new module.
7) Insert the reserved words back to where they belong. Create new variable names as necessary.

And that should work. The code will be back to functioning as well as it did before your made the gaff (in my case, poorly .)

 

[RoryA]

Try adding:

Public Count as Long

to the top of a module, then compile the project, then remove that line again.