2007 macros (or earlier) not working with 2013

[Hiker]

I'm practically illiterate with macros but took over a membership workbook with ~2000 members. Various macros distinguish members geologically, alphabetically, nicknames, etc. and makes various lists with Word (not all the macros do this).

Most are stopping with run-time error: 13 type mismatch (is this case in yellow text near end of code)

Here's an example:

Sub GeogDist()
    
    ' Creates geographic distibution of members and stores counts in
    ' States worksheet.


    ' Define working sheets and their relevant columns.


    Dim MemberSheet
    MemberSheet = "Members"
    Dim MemNo
    MemNo = "MemNo"
    Dim StateCol
    StateCol = "state"


    Dim StatesSheet
    StatesSheet = "States"
    Dim StAbb
    StateAbbrev = "StAbb"
    Dim CountCol
    CountCol = "Count"


    Dim StateArr(63, 1)


    Sheets(StatesSheet).Select


    ' Determine the last row and column of the worksheet
    lr = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    lc = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column
    
    ' Determine the StateAbbreviations  column
    For i = 1 To lc
        If Worksheets(StatesSheet).Cells(1, i).Value = StateAbbrev Then
            sa = i
            Exit For
        End If
    Next i
    
    Cells(2, sa).Select
    
    ' Create array of state abbreviations
    For i = 0 To 63
        StateArr(i, 0) = ActiveCell.Value
        ActiveCell.Offset(1, 0).Select ' Move down one row
    Next


    ' Determine the member count column
    For j = 1 To lc
        If Worksheets(StatesSheet).Cells(1, j).Value = CountCol Then
            uc = j
            Exit For
        End If
    Next j




    Sheets(MemberSheet).Select


    Dim strStartCell As String
    Dim strEndCell As String
    Dim strFullColumn As String




    ' Determine the last row and column of the worksheet
    lr = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    lc = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column


    ' Determine the number of members column
    For i = 1 To lc
        If Worksheets(MemberSheet).Cells(1, i).Value = MemNo Then
            mn = i
            Exit For
        End If
    Next i
    
    ' Determine the states column
    For i = 1 To lc
        If Worksheets(MemberSheet).Cells(1, i).Value = StateCol Then
            sc = i
            Exit For
        End If
    Next i
    
    ofst = sc - mn ' create the column offset differential btwn memno and state


    Dim rng As Range




     ' For each member record, add the member count to the state total
    For Each rng In ActiveSheet.Range(Cells(2, mn), Cells(lr, mn))
        State = rng.Offset(0, ofst).Value
        Members = rng.Value
        For i = 0 To 63
            If StateArr(i, 0) = State Then
[COLOR=#ffff00][B]                StateArr(i, 1) = StateArr(i, 1) + Members[/B][/COLOR]
            End If
        Next
    Next




    ' Write member counts to the States spreadsheet
    Sheets(StatesSheet).Select
    Range(Cells(2, uc), Cells(65, uc)).Clear ' Remove previous counts
    Cells(2, uc).Select
    
    For j = 0 To 63
        ActiveCell.Value = StateArr(j, 1)
        ActiveCell.Offset(1, 0).Select ' Move down one row
    Next


End Sub

 

[timfritsch]

Shot in the dark: change Dim StateArr(63, 1) to Dim StateArr(0 to 63,1). Rerun the report and see if you get the error in the same spot.


Tim

 

[DocAElstein]

I....Re: 2007 macros (or earlier) not working with 2013.....

are you saying that the macro works with earlier versions??

 

[Hiker]

are you saying that the macro works with earlier versions??

Yes it did. My laptop with Office 2010 crashed and Microsoft gave me the option of installing Office 2013 on the replacement. I restored the files from a Mozy back-up and the files opened fine, but none of the macros worked.

As far as I know the macros may have been written using 2003 or 2007 software.

 

[Hiker]

Shot in the dark: change Dim StateArr(63, 1) to Dim StateArr(0 to 63,1). Rerun the report and see if you get the error in the same spot.


Tim

Yes error is the same.

 

[DocAElstein]

Yes it did. My laptop with Office 2010 crashe.....

As far as I know the macros may have been written using 2003 or 2007 software.

Hi Hiker,
.. I did have some issues with conflicting Library references with Functions going from 2010 / 2013 back to 2007. They were overcome mostly by being a bit more explicit and referencing with an extra
VBA. ( so for example instead of

=Left(_____,_____)

One would write

=VBA.Left(____,____)

…)
. This more explicit version ensures referencing was going , as it were to the “Old / standard “ Library rather than a new one that was added with a newer version of Excel and which somehow seemed to confuse and give strange errors.. IF and only if the program had been written on the earlier versions.
. At first glance the Function referencing does not appear a problem here

. Another option in a similar fashion though to the solution I had above would be to re-write the code in a 2013 module ( not literally, you could paste parts of the code to a text file, then paste to a fresh module ). Some times peculiar problem of this nature have been solved by me that way. Note then however you may have a reverse problem trying to run that code in earlier versions.

. Other than that I cannot help further – without the relevant data, your code, of course will not run by me and crashes everywhere so debugging is impossible.
. - I assume you are sure that it is a 2013 problem . – you still have somewhere access to earlier versions of excel to confirm he programs still work correctly.??
. If not, and you had a simpler program that required little or no test data which you could supply, then I could run that here later (I have 2003, 2007, and 2010 ) and confirm for you, at least, that on earlier versions the code was still working

. Sorry I cannot be more help. Someone would need access to XL 2013 and test data to run / help with debugging your code further.

Regards
Mustava Klatsche

 

[RoryA]

The most likely cause of the error would be if the Member variable contains an error or non-numeric value. What is its value when the error occurs? (If you click debug when the error happens, then hover the cursor over the word Member in the code, you should see a tooltip with its value in.)

 

[Hiker]

Thanks for your help gentlemen I'll see id I can narrow down the problem and ask the old memsec if the macros work on her machine.

 

[DocAElstein]

Thanks for your help gentlemen I'll see id I can narrow down the problem and ask the old memsec if the macros work on her machine.

yous welcome, WEs glad to help! (or try!)