Converting Excel 2003 Macros to 2010

Hi

I have come across this site http://technet.microsoft.com/en-us/l...r_Installation

I have followed the instructions and in my downloads I have a file called OCCI release.

I have run that file and it as created a folder called C:\OCCI

In that folder is a file called OCCI2010 Update1 I have run that file.

But the new icon Inspect VBA code does not appear on Developer can someone give m,e step by step instructions on how to do this I am obviously doing something wrong.

Many Thanks

Okay Perhaps not enough information above.

I have run the download again and found that there was a GB version so I tried that but I am getting the same result.

If I go to File Options Add Ins The Microsoft Excel Code Compatability Inspector is under the Inactive Application Add Ins. So I have clicked it and Selected Manage COM Add Ins and Go.

I get a box with Add Ins available and the Microsoft Excel Code Compatability Inspector is there.

the Location is given and underneath is says Load Beahaviour: Not Loaded The managed Add In Loader failed to initialize.

If I tick the Add In and click OK that doesn't change and if I click Add it takes me to Roaming\Add Ins to the filename box but if I type in a anme it says it can't find it.

Any ideas anyone?

What is it you want help with?

Is it getting the add-in to work or with the converion of code?

Hi,

There have been no further posts re my original enquiry. I am interested can anyone else get the following download to work? If they can can they tell me how?

I have Windows 7 and Office 2010.

I download it to my desktop okay and double click the icon but I don't get any more prompts after that and the site suggests there should be some?

http://www.microsoft.com/en-gb/downl....aspx?id=15001

Sorry for not getting back to you.

I downloaded the add-in and it works fine.

When you run OCCI.exe it should ask you where you want to extract the setup file, the default directory being C:\OCCI.

The file extracted is called OCCI20102Update.msi.

Do you have that file?

Hi Norie,

Yes I do. in a directory called C:\OCCI.

What happens when you run it?

I assume you do this by right clicking and selecting install?

I get a box which has two options -

Repair Microsoft Office Code Compatability Inspector Update 1
Remove Microsoft Office Code Compatability Inspector Update 1

That means you have it installed and it should load when Excel is started.

Perhaps you should try removing it and re-installing it.

Okay.

I uninstalled it and then installed it and got some windows I hadn't had before. I went all the way through and it seems to be installed but there is still not sign of the extra options on the Developer Ribbon.

In File Options Add Ins it is in teh section called Inactive Add Ons.

I tried selecting the COM Addins in the Manage Box and the Go Button and that brought it up in a seperate window with a tick box. I ticked the box and no difference?

Try closing and re-opening the workbook after you've ticked the box.

No Luck.

The Comm Add Ins box which comes up if you click the COM Addins Icon in the devloper tab ays at the very bottom -

Load Behaviour: The Managed Add In Loader failed to initilaize.

whatever that means!

Did you deselect the Visual Studio option?

No. so I have uninstalled and reinstalled and still no luck.

Sorry I'm at a loss now, that was my last shot really.

I had a bit of a search but didn't find much beyond re-installing Excel.

That was for add-ins in general, not this specific one.

I know you want to use this tool because you want to look at converting 2003 code to 2010, but are you sure
you actually need to convert anything?

I've tried this tool in the past and, to me anyway, some of the comments were a bit vague, even unhelpful.

For example saying line of code was deprecated but not saying which particular part of the code.

What 2003 code have you had fail at home when you tried it on 2010?

I have a workbook which has a set of macros that work fine in Excel 2003 but the workbook keeps crashing in 2010. It must be the macros but I don't know what to look for. I can't post the workbook because it is too big and has sensitive information, I could post the macro's (there are about 14 in all) if you were willing to have a look at them.

No problem.

How/when is the workbook crashing?

Is it when you open it in 2010?

No it's when I try and save it. So I can load it in 2003 format but if I save it I usually end up with Not Responding. If I save it as Macro Enabled it works sometimes but then it usually ends up Not responding as well.

I have stripped out all the worksheets except one, deleted all the information and attached the macros. The Membership Worksheet has an embedded macro and there are modules with the macros in.

I've no problem opening the workbook in Excel 2010 and then saving it as xlsm.

I took a quick look at the code and as far as I can see there's nothing there that's incompatible with 2010.

The code could maybe do with a bit of a tidy though, getting rid of all the Select/Activate stuff might be a start.

I also tried the buttons and they worked with only 2 errors that I think can be put done to the lack of data and missing worksheets.

PS The Add a New member button's a bit weird, skipping from cell to cell - you might want to turn off screen updating.

Thanks for having a look Norie.

Can you give me a small example of how to get rid of the Select/Activate stuff to work with?

It's pretty hard to give a good example involving the more complicated code.

I've tried but I always seem to come to a point where I just don't know what cells are being referred to on which sheet.

Here's a very simple example.

Sub Macro11_Prep_Rents()
    With Sheets("MEMBERSHIP BOOK")
        .Range("AL8:AL600").ClearContents

        .Range("AN8:AN600").ClearContents

        .Range("AP8:AP600").ClearContents

        .Range("AR8:AR600").ClearContents

        .Range("AT8:AT600").ClearContents

        .Range("AV8:AV600").ClearContents

        .Range("B5").Value = 366
    End With

End Sub

Here's one of the subs I had trouble with.

'Copy All New & Current Members (Blakes)
'From Rents
'To Rents_Blakes

Sub Macro03_Update_Blakes_Rents()
Dim Ws1 As Worksheet, Ws2 As Worksheet, lastA As Long, i As Integer
    Set Ws1 = Sheets("RENTS")
    Set Ws2 = Sheets("RENTS_BLAKES")
    Application.ScreenUpdating = False

    Ws2.Select
    Range("A8:AZ600").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("A1").Select

    With Ws1
        For i = 8 To 600
            If UCase(.Range("J" & i).Value) = "BLAKE'S" Then
                If Ws2.Range("A600").End(xlUp).Row < 8 Then
                    lastA = 7
                Else
                    lastA = Ws2.Range("A600").End(xlUp).Row
                End If
                .Rows(i).EntireRow.Copy Destination:=Ws2.Rows(lastA + 1)
                Application.CutCopyMode = False
            End If
        Next
    End With

    Range("A8").Select
    Range("A8:AZ75").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                    :=False, Transpose:=False


    Range("A8:AZ" & Range("B65536").End(xlUp).Row).Sort Key1:=Range("J8"), _
                                                        Order1:=xlAscending, Key2:=Range("A8"), _
                                                        Order2:=xlAscending, Header:=xlNo

    Set Ws1 = Nothing
    Set Ws2 = Nothing

    Application.ScreenUpdating = True
End Sub

The first part is easy to deal with.

    Ws2.Range("A8:AZ600").ClearContents

The With End With after that part is fine, everything referenced correctly.

It's this part that's the problem.

 Range("A8").Select
    Range("A8:AZ75").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                    :=False, Transpose:=False


    Range("A8:AZ" & Range("B65536").End(xlUp).Row).Sort Key1:=Range("J8"), _
                                                        Order1:=xlAscending, Key2:=Range("A8"), _
                                                        Order2:=xlAscending, Header:=xlNo

It's easy to get rid of the Selects, but which worksheet is A8:AZ75 on? Is it Ws1 or Ws2?

    
    With Range("A8:AZ75")
      .Copy
      .PasteSpecial Paste:=xlPasteValues
    End With

Hi Norie,

No even I am not that much of a numpty. I meant that I could send you the workbook with all the tabs in place if that would help? I deleted them all to make the workbook small enough to post last night.

But I could strip out all the data and leave the sheets in place if you wanted to have a look.

Having all the sheets in place might help but I'm not sure.

One thing I could do is run it through the Compatibilty Inspector thing.

Not sure how you can send it to me though.

Hi Norie,

If you could run that Compatability Inspector I would be grateful. I really don't know what to do otherwise. All I do know is that something in the file is making it crash and as far as I can see it can only be the macros.

The macros also run much slower in 2010.

God knows what I'll do when we migrate to Windows 7 at work, I have hundreds of macros which will need attention.

I've attached the file with the commentst the Compatibility Inspector added and the report it produced.

Thanks Norie, wish I knew what it meant. Do I glean from all this that it doesn't like this line of code ?

ActiveCell.Offset(1, 0).Range("A1").Select

Kind of thought you might say that.

To be honest I don't fully understand it myself and I don't think the links are much help.

As for that line of code, the Range("A1") part is redundant.

It refers to the first cell in the range, but since there's only one cell in the range it does nothing.

Thanks for all your time Norie. A pity they didn't leave the code part alone so that the old code worked.