+ Reply to Thread
Results 1 to 6 of 6

Compare two reports-Working but need help to automate the testing process

  1. #1
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Post Compare two reports-Working but need help to automate the testing process

    BACKGROUND:
    As some of you already know, I am seeking knowledge on Application properties. (Specifically I want to know:
    1. Which values are read-only or not?
    2. Of the values that are not read-only, which changes are reset by Excel?
    3. Of those values reset by Excel - WHEN are they reset? (i.e. at sub end, at workbook close, at application close)
    )

    I have raised the question as to whether there is any webpage somewhere out there which has a summary of the lifetime of changes made to the Application Properties (see: http://www.excelforum.com/excel-prog...roperties.html) however so far there has been no result.



    THE CODE SO FAR:
    In the meantime, I thought I would create code that would report on the values of Application Properties. Having done this, I set out to create a macro that would compare two of these reports. (If anyone reading this is familiar with a program called regshot - you will instantly know where I got the idea and what I am trying to achieve. ) This would enable someone to run/save a report, create a dummy sub to change some of these values and then run/save a second report. The comparision macro will enable to see which values have been reset by comparing the results of the 'before' and 'after' report.

    And it appears to be working. See workbook attached. (Please excuse messy code in comparison macro. Also this is intended to be a XLAM however I posted it here as a XLSM to make it more accessible).

    Now the problem (as stated in the thread title) is this: I need a macro to automate this testing process. And I have no idea of how to begin. (I'm a VBA rookie. Please be patient!)



    COULD SOMEONE PLEASE PROVIDE THE FOLLOWING CODE:
    I need an "automated tester" macro. It would need to:
    1. run a first (before) report
    2. change the values of all the properties (the alternative values would probably need to be tested beforehand to ensure that they really are valid values for that property)
    3. run the second (after) report
    4. compare the two reports

    Steps 1, 3 & 4 are already created. It would be just a matter of calling these from the "automated tester" code.

    The tricky bit (as I see it) will be 1. determining the alternative values for each property in Step 2 and 2. testing whether these values are reset at sub end/WB close/App close.
    Changing the values could be called from a separate sub so doing that would automatically test the first possibility (reset at sub end).
    Attached Files Attached Files
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  2. #2
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Compare two reports-Working but need help to automate the testing process

    I have been researching the 'writeability' of the properties & their value types. Please see attached workbook for my notes so far.

    It would appear that there are only 97 properties that can be read/write (that's not including properties defined as being return or set). So that greatly narrows the amount of testing required.

    I am having some trouble determining the value types for all of the properties. I am particularly after the value types for properties marked as read/write (e.g. Application.Cursor) I suspect most of these are Long but I want proof not assumptions.

  3. #3
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Compare two reports-Working but need help to automate the testing process

    Why don't you just look in the object browser?

    The object browser says that Application.Cursor accepts a long value - the enum for this value is XLMousePointer which is a member of the Excel object:
    Enum Name Value
    xlDefault -4143
    xllBeam 3
    xlNorthWestArrow 1
    xlWait 2
    Last edited by Kyle123; 04-23-2013 at 05:36 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Compare two reports-Working but need help to automate the testing process

    Quote Originally Posted by Kyle123 View Post
    Why don't you just look in the object browser?
    Because I don't know how! (I'm will be doing a VBA course soon. I haven't done one yet).

    This looks like it could really speed up solving my post #2.

    How do I get the table that you posted? I've opened VBA editor, F2, Selected Application, Selected Cursor, now what?

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Compare two reports-Working but need help to automate the testing process

    Click on XlMousePointer at the bottom where it says :
    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: Compare two reports-Working but need help to automate the testing process

    Quote Originally Posted by Kyle123 View Post
    Click on XlMousePointer at the bottom where it says :
    Please Login or Register  to view this content.
    Thanks for the help Kyle.

    Unfortunately I can't obtain default values for all Application properties this way. (some examples: DisplayAlerts, FormulaBarHeight etc.) Oh well, back to the drawing board.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1