none
Testing macro RRS feed

  • Question

  • I've created a macro for a client with employees around the country, all of whom have different versions of Excel on their computers.  Is there a way for me to test my macro on various Excel versions to make sure it works with them all?  I created mine on Excel for Mac 2011 and the client's machines are all Windows.

    Thanks very much for any help you can give me.

    Monday, June 29, 2015 4:40 PM

Answers

  • Yes, I do the same for different languages and Windows versions, by using a virtual machine.

    I'm using Virtual Box from Oracle:
    https://www.virtualbox.org/

    A virtual machine means you can have a PC in a PC.

    But running 2 (or more) PC's simultaneously on one real PC means your main machine should have enough power, IMO at min. 8Gb of RAM and min. 3 CPU kernels. (Better have >=16Gb RAM and >=5 CPU's).

    Otherwise the performance of the virtual machines (and there handling) becomes very slow.

    Andreas.

    Monday, June 29, 2015 5:56 PM

All replies

  • Yes, I do the same for different languages and Windows versions, by using a virtual machine.

    I'm using Virtual Box from Oracle:
    https://www.virtualbox.org/

    A virtual machine means you can have a PC in a PC.

    But running 2 (or more) PC's simultaneously on one real PC means your main machine should have enough power, IMO at min. 8Gb of RAM and min. 3 CPU kernels. (Better have >=16Gb RAM and >=5 CPU's).

    Otherwise the performance of the virtual machines (and there handling) becomes very slow.

    Andreas.

    Monday, June 29, 2015 5:56 PM
  • Thanks, Andreas.  That's helpful.  This still means that I have to own a copy of each version of Excel, though, right?  I was hoping I could find a way to run the macro on various versions without having to buy them all.

    Any ideas?

    Charlie

    Monday, June 29, 2015 6:44 PM
  • Show us your code.

    Some funcionalities has a reference at the internet of compatibility with the various versions

    If you need test in Office 2013 Professional Plus 64 bits, You can count on me



    Monday, June 29, 2015 7:51 PM
  • Hi Andre,

    I've actually just started writing it, but this is what I've got so far:

    Sub Macro4()
    '
    ' Macro4 Macro
    '
    '
        ExecuteExcel4Macro "WINDOW.MOVE(102,-43,"""")"
        Dim strFile As String
         
        strFile = Application.GetOpenFilename
        If strFile <> "False" Then Workbooks.Open strFile
        Sheets("Master").Visible = True
        Sheets("Master").Select
        Range("E1037").Select
        Selection.Copy
        Sheets("Cover Page").Select
        Windows("test MACCU.xlsx").Activate
        Range("I5").Select
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        
    End Sub

    I need the macro to go pull information from a file which the user will be choosing and paste it into a template.  The file the user will choose will always have the same format and the same tabs, but always will have a different filename.

    I think most of the people use Excel 2010, but I haven't heard for sure yet.

    Thanks,

    Charlie


    Monday, June 29, 2015 10:03 PM
  • Hi robinsong,

    About excel object model changes, you may refer to these articles below:

    # Excel 2010 Object Model Changes Since Earlier Versions

    https://msdn.microsoft.com/en-us/library/office/ee836187(v=office.14).aspx

    # Compatibility issues in Office 2013

    https://msdn.microsoft.com/en-us/library/jj229903.aspx

    To test the macro in different version of excel file and different environment, as Andreas said that, you need to do tests in the virtual machines and I think you need to buy the product.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 30, 2015 6:58 AM
    Moderator
  • Hi Starain,

    Useful articles, but somewhat depressing to see how much changes from version to version!  

    I have found out that the company has only Excel 2010 and 2013, so I think I'll buy 2010 and take Andre up on his offer (thank you, Andre!) to test out my final macro on his Excel 2013.  If my macro will work on Mac 2011, Windows 2010, and Windows 2013, I think it will be pretty bombproof.

    Thanks for all the help, everyone!

    Charlie

    Tuesday, June 30, 2015 1:23 PM
  • I think you will not have problems with this code using 2010 or 2013, except for the function ExecuteExcel4Macro, that I really don´t know.

    When you finish the macro, tell me that I test for you 

    Tuesday, June 30, 2015 2:20 PM
  • Thanks, Andre.  When I get the thing finished, it will be much more polished (I hope) and I'm not even sure that line will be there.  The only part I'm at all worried about is asking the user to choose the file to open.  Seems like that's something that could be different in different versions.
    Tuesday, June 30, 2015 2:47 PM
  • Hi robinsong,

    We could use Applicaton.FileDialog property to accomplish choose file function.

    # Application.FileDialog Property (office 2013, 2010, 2007)

    https://msdn.microsoft.com/en-us/library/office/ff836226.aspx

    https://msdn.microsoft.com/en-us/library/bb208503(v=office.12).aspx

    If you have anything issue in feature, please feel free to open the new thread.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 1, 2015 1:44 AM
    Moderator
  • Thanks, Starain,

    I will keep this in mind as I try out the macro on the PCs.  I will almost certainly be opening a new thread soon, too.  The next part of the macro involves copying a range of information from a spreadsheet and pasting it in a specific place in a Word document.  I've just started working on it.  So far the trickiest piece is determining how to make the range paste in exactly the right place.

    More later,

    Robinsong

    Wednesday, July 1, 2015 11:28 PM
  • Hi Robinsong,

    If you can’t deal with it by yourself, please feel free to open the new thread.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, July 2, 2015 3:11 AM
    Moderator