Macros working in previous versions don't work in 2013

Greetings,

My problem is as follows:

My client, does a lot of work with Excel macroes. The continuous macro they've been using for the past 10 years, has worked in Office XP, 2000, 20003, 2007 and 2010, however, it DOES NOT Work in Office 2013.

Macro package:
Directories: c:\Kosovnice
Files: proba3.xls <--- file containing the macros
FILBAZA.xls <---- file containing raw data (lenght, height, width, thickness of selected objects)
Kosovnica.xls <---- simple table of contents on which the pasted data fetched by the macro is shown

Ussual procedure: 
1. Run excel, if version is post 2007, security -> Enable macros and access to VBA.
2. Open proba3.xls to insert the additional toolbar. Close the worksheet.
3. Open Kosovnice.xls and work with the added toolbars to paste information into different parts of the list.

XP, 200, 2003 required nothing except the running of the macro xls file first to insert the macro into Excel. 
Later versions - namely 2007 and 2010, prior to 2013, required the "Enable Macros" and "Enable access to VBA" settings in the Security to be clicked. Worked fine afterwards.- 
2013, even after all the macro and VBA access is enabled, the macros still don't work. All I get is the error : "Run-time error '9': Subscript out of range"

If I click "Debug", the VB debugger highlights this line in yellow. aValue = Worksheets("Prenos").Range("E3")

The script for each module (there is 15) is as follows:

----------BEGINING OF PASTED CONTENT--------------
Dim aValue As Variant
Dim bValue As Variant
Dim cValue As Variant
Dim dValue As Variant
Dim eValue As Variant
Dim fValue As Variant
Dim gValue As Variant
Dim hValue As Variant
Dim iValue As Variant
Dim jValue As Variant
Dim kValue As Variant
Dim mValue As Variant
Dim firstbook As Object

Sub IzberiPleh()
    Set firstbook = ActiveWorkbook
    ChDir "C:\KOSOVNICE"
    Application.ScreenUpdating = False
    Workbooks.Open Filename:="C:\KOSOVNICE\FILBAZA.xls"
    Windows("proba3.xls").Activate
    If DialogSheets("Dialog3").Show = True Then
    firstbook.Activate
        Selection.Cells(1).Offset(0, 1) = aValue
        Selection.Cells(1).Offset(0, 2) = bValue
        Selection.Cells(1).Offset(0, 3) = cValue
        Selection.Cells(1).Offset(0, 4) = dValue
        Selection.Cells(1).Offset(0, 5) = eValue
        Selection.Cells(1).Offset(0, 6) = fValue
        Selection.Cells(1).Offset(0, 7) = gValue
        Selection.Cells(1).Offset(0, 8) = hValue
        Selection.Cells(1).Offset(0, 9) = iValue
        Selection.Cells(1).Offset(0, 10) = jValue
        Selection.Cells(1).Offset(0, 11) = kValue
        Selection.Cells(1).Offset(0, 12).Formula = "=E:E*G:G*I:I*J:J*0.00000785"
        Selection.Cells(1).Offset(0, 13) = mValue
    End If
    Windows("FILBAZA.xls").Close
    Selection.Cells(1).Offset(0, 6).Select
End Sub
Sub ListPleh()
    Windows("proba3.xls").Activate
    aValue = Worksheets("Prenos").Range("E3")
    bValue = Worksheets("Prenos").Range("F3")
    cValue = Worksheets("Prenos").Range("G3")
    dValue = Worksheets("Prenos").Range("H3")
    eValue = Worksheets("Prenos").Range("I3")
    fValue = Worksheets("Prenos").Range("J3")
    gValue = Worksheets("Prenos").Range("K3")
    hValue = Worksheets("Prenos").Range("L3")
    iValue = Worksheets("Prenos").Range("M3")
    jValue = Worksheets("Prenos").Range("N3")
    kValue = Worksheets("Prenos").Range("O3")
    mValue = Worksheets("Prenos").Range("Q3")
End Sub
---------END OF PASTED CONTENT-----------------

I really need to get this working, otherwise my clients purchase of Office 2013 was a kick in the dark. And as far as I've spoken with CS there is no downgrade avaliable for Office 2013 OEM.

I'm also willing to send/apped the files and explain how they work for anyone who is willing to help to test on previous version and try and figure out how to get it to work in 2013 without rewriting the whole thing all over again.

Best regards,
Aleš Mikolič
Service Department
3arh d.o.o.
 
Question Info

Last updated July 20, 2018 Views 54,962 Applies to:
Answer

Jan Karel, You are correct - the code was having difficulties communicating between the different Excel Windows. 

In operation they would open the one file that has the old DialogSheets in it that created the custom menu in Excel for them and had the functional code in it.  Then they would open the second file that simply held a kind of invoice sheet that was built through the actions of the custom menu and dialog sheets code -- while doing that, the 3rd workbook was opened to extract data from.

 

What I did was to combine the first 2 workbooks into a single workbook, in effect creating a "front end" to the 3rd file which acts as the back end of their Excel database.  This is now working quite well according to the OP in an email to me.

 

I actually did something like your recommendation in the code itself!!  There are 15 pairs of routines in the code (15 DialogSheets) - the code shown in the OPs first post for the Sub Sub IzberiPleh() sets firstbook to refer to the workbook with the Prenos sheet in it and Sub ListPleh() is called by the DialogSheet.  So I changed the routines to look something like this:

Sub ListPleh()
    aValue = firstbook.Worksheets("Prenos").Range("E3")
    bValue = firstbook.Worksheets("Prenos").Range("F3")
    cValue = firstbook.Worksheets("Prenos").Range("G3")
    dValue = firstbook.Worksheets("Prenos").Range("H3")
    eValue = firstbook.Worksheets("Prenos").Range("I3")
    fValue = firstbook.Worksheets("Prenos").Range("J3")
    gValue = firstbook.Worksheets("Prenos").Range("K3")
    hValue = firstbook.Worksheets("Prenos").Range("L3")
    iValue = firstbook.Worksheets("Prenos").Range("M3")
    jValue = firstbook.Worksheets("Prenos").Range("N3")
    kValue = firstbook.Worksheets("Prenos").Range("O3")
    mValue = firstbook.Worksheets("Prenos").Range("Q3")
End Sub

 

And it works quite well -- and as you can see, this is just a longer way of doing EXACTLY what you have recommended.  And it had to be done even though I'd combined the 2 workbooks, because at the time these List...() routines are called, the 3rd Excel file containing the database itself is open and active.

I am free because I know that I alone am morally responsible for everything I do. R.A. Heinlein

8 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer

First - disregard my email asking if certain changes to the custom menu would be acceptable - that turned out not to be needed.

 

I have combined 2 of the workbooks into a single workbook and made some code changes so that it will work that way.  This has permitted it to work in Excel 2013 for me here, and still work in Excel 2010 also.  Since I have sent it to you as a .xls file, I believe it should now work in all currently in-use versions of Excel such as 2000, 2003, 2007, 2010 and 2013.

 

I hope that this works as well for you as it has been for me here.  It has worked in Excel 2003, 2010 and 2013 for me here:  (Excel 2003 and 2010 under Windows 7, Excel 2013 under Windows 8).

 

Also - you should no longer have to enable that "Trust access to VBA Project Object" setting in macro security, and you should try clearing that and running the new workbook without it set.  You still have to enable macros to run at some point, of course.

I am free because I know that I alone am morally responsible for everything I do. R.A. Heinlein

23 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.