P.E.T. - Keep your pets organized

WarcraftPets Profile: Ryazan · Post by **Ryazan** » February 22nd, 2013, 9:41 am

Hello!

(Some Wall of Text incoming so please bear with me. If you are impatient, skip directly to the "3. How to get the workbook and what it requires" -part. :)

1. Background

I've been working on a pet-related project for quite a while now and now the project is finished and ready to be published.
This is all began when Darky28 added an export feature to his awesome website which allows users to export their battle pets to their own computer in an Excel-workbook. The Excel output is pretty much raw data but it gave me an idea that could you build another Excel-workbook around it which has lots of enhanced tools and options to handle your pets. Well, of course you can, it was just a matter of time, patience and determination which I happen to have when it comes to pets. Adding sorting and filtering to the output is simple stuff but I wanted to do more with my workbook, much more. :)

So Pet Empowered Tool (P.E.T.) was born.

2. What it is

P.E.T. is a macro-enhanced Excel workbook which allows you to import your pets from Petsear.ch's Excel output and then do different kind of things with them™. Ok, that probably didn't you tell you much but I'm getting to it! :) Anyhow, the workbook is powered by macros and it has a good 1,7k lines of VBA code in it - needless to say, creating it wasn't a quick task. Workbook is protected and tested rather well from user misuses and shouldn't break down unless the user delibrately start looking for holes. I've done the workbook with Excel 2010 but it does work with Excel 2007 too. OpenOffice however isn't unfortunately supported and I have no intentions of making an OO version.
But enough of the tech stuff, show me the features!
- Main view allows the user to sort and filter his/her pets pretty much by anything
- User can preview his/her pets' stats in any quality and level via Stat Calculations
- Main view also indirectly supports Breed IDs (more of this later in the post)
- Tons of color coding for duplicates, quality, max level pets, gender and breed ID to help usability
- Collection statistics for all pets or unique pets: breakdown by school, quantity, quality and number of maxed pets along with average level
- Statistics also have gender and breed ID breakdown for all pets (why only for all pets is explained later)
- Possibility to show collection's pet score for WarcraftPets.com and WoWProgress.com

3. How to get the workbook and what it requires

You can download the workbook from

http://cryados.net/wow/pet/pet_105.xlsm

Like earlier stated, you will need Microsoft Office 2007 or newer in order to use it. You also need to enable macros when you open the workbook since macros make the workbook tick and without them it's like a car without engine - looks (probably) pretty but doesn't drive you to work.

4. Getting started with P.E.T.

Basically, you always need to import pets first. The instructions for that can be found behind the Import-button but to briefly recap them: you get your pet Excel via Petsear.ch, copypaste them into my workbook and press a button. Voilá! You can now move to explore the main view or go to the collection statistics (note that you can always re-import your pets but it will always overwrite current collection).

Main view (pet listing):
- * Sorting and filtering your collection
  In the main view, you have all your pets listed and there are several headers in the columns by which user can sort or filter the pets. This is done by pressing the small button in the header cell with an arrow pointing down. After that you can sort your pets by that column or filter only certain pets for viewing (viewing only uncommon pets for example). You can also mix filters as much as you please (like picking only lvl 1 uncommon dragonkin pets).
  You can always reset these filters to the default sorting (first descending by level and then ascending by name) by pressing the "Reset filters" -button.
  
  * Breed IDs
  I mentioned in the features that the workbook supports indirectly breed IDs so I'll explain how. Darky28 has also provided an Excel output for every breed of every pet and my Excel uses that. So assuming you have a valid pet in the row (valid = pet's species ID is given) and you then select the Breed ID cell from that row, a popup is shown. In the popup is listed all the possible breeds for the current pet and user can do his/her pick from there and save the changes. However, you can also test breed IDs for breeds that doesn't exist by simply selecting them from the breed ID cell's dropdown box.
  
  * Stat calculations
  To open up stat calculations, you need to press the respective button in the main view. In the first three columns of stat calculations is shown HP, Power and Speed for the current level and quality and in the latter three columns the values user can define from the stat calculation options. You could check how does your collection look like if all your pets were be legendary. :p
  
  * Adding pets manually
  While the workbook is based on importing the collection, nothing prevents you from adding pets manually. Basically you need you input name, school, quality, level and species ID manually and then select breed ID from popup or from dropdown if the species ID isn't yet in the workbook. Base stat info won't be added however but they are only needed in the stat calcuations anyways. If you are missing some "vital" pet detail, a red X will appear to the left-most column so can you quickly see what pets are missing some info.
  
  * Deleting pets
  A pet is deleted simply by selecting any cell from the row where the pet lies and pressing the "Delete selected pet(s)" -button. For deleting a range of pets, just extend the selection for several rows and all pets in those rows will be delete. Note that you can't undo the deletion. It is advised you delete pets via the button but you can also delete them manually by selecting a range starting from the Name column and ending to the Base Speed column and pressing delete. This will leave an empty line however and will require sorting to get rid of it.
Collection statistics:
- I won't go much into the details in the collection statistics as they are pretty straight-forward. User can select from the dropdown box whether to show stats for all pets or only for unique pets. Calculating stats for unique pets takes a bit time but we are talking about 5-10 seconds.
  
  Gender and Breed ID breakdown can be found in the bottom part of the page and like I said above, they are calculated from all pets. I can't see a way to calculate them from unique pets as how can you decide from two same pets with different breeds which one of them will be counted and be the unique pet of that species ID. It just isn't sensible and same goes with gender.
  
  Lastly, there is a "Show pet scores" -button in the top of the page to show user both of the "used" pet scores around, WarcraftPets and WoWProgress. To calculate accurate WCPets' petscore you have to input the amount of your non-combat pets as Petsear.ch's Excel only lists the battle pets. You can find the amount of your non-combat pets from your Petsear.ch's page or from your WCPets profile. Once you have inputed the amount once, the workbook will remember it. When you are done with non-combat pets, pressing the Calculate-button will show both of your pet scores.

5. Known issues

- If you sort your pets descending by any of the column in the stat calculations, a bunch of empty lines will pop to the top of the list. This is because those cells all have formulas in them so they aren't counted as empty cells by the sort. This can't be fixed BUT you can filter out the empty lines from the column's filtering options first and then do the sort.

- A slight downside in the workbook is it takes a while to calculate stats for unique pets since there are quite a few macros running during that point but user will be notified about this.

I can't think of any other glitch from the workbook but if you encounter some bug or other anomality, please report them here and I'll look into it.

6. Final words

First of all, every thanks REALLY goes to Darky28. His hard work on petsear.ch is the only reason why my workbook exists. So please pay that great site a visit and support him. :)

Other than that, well... not much more to say other than I hope someone else finds my workbook useful. I can't think of any other place where to publish this than in WarcraftPets since if I had to pick an adjective to describe this community, it would be "dedicated."

It's been challenging to create P.E.T. and making every feature I wanted but I can say I'm satisfied to it's current state and it's been useful in my own pet adventures. So enjoy it and thanks for reading if you actually read all the way down here. :)

-Ryazan

Gendou · Post by **Gendou** » February 22nd, 2013, 10:59 am

Intriguing...

... damn it, now I know what I'll be doing today.

GrosBonda · Post by **GrosBonda** » February 24th, 2013, 11:29 am

Very Intriguing...
worth a good try .....

WarcraftPets Profile: Talmar · Post by **Talmar** » February 26th, 2013, 11:41 am

I'll have to check this out. Looks interesting.

Tekulve2012 · Post by **Tekulve2012** » February 26th, 2013, 12:31 pm

My thanks to the authors of these programs .. i use petsear.ch regularly to look up stats on my collection and breed info
for 'new to me' pets I acquire

thanks for all your work !

WarcraftPets Profile: Ril · Post by **Ril** » March 3rd, 2013, 4:51 am

ohhh very nice. exactly what i was looking for. thank you!

WarcraftPets Profile: Ryazan · Post by **Ryazan** » March 7th, 2013, 2:24 am

Updated version for 5.2 pets.

WarcraftPets Profile: GormanGhaste · Post by **GormanGhaste** » March 7th, 2013, 7:08 am

This looks like fun! Will have to try it out.

WarcraftPets Profile: Ryazan · Post by **Ryazan** » March 21st, 2013, 7:17 am

Another small update to WCpets pet score calculation due to the non-combat pet quality change.

Lordblacknail · Post by **Lordblacknail** » March 24th, 2013, 5:07 pm

Well, first thanks for the time and energy you put into this.

Second, how about telling us old guys how to get it started. I dl the files, don't see anything that looks like a spreadsheet. Then I dl my pets from petsear.ch and have them open in a spreadsheet. How do I get your stuff to my spreadsheet? Push a button and Voila doesn't tell me much. Which button?

Detailed instructions on what to do after I have my pets from Petsea.ch open in Excel would be great. Thanks so much in advance.

WarcraftPets Profile: GormanGhaste · Post by **GormanGhaste** » April 30th, 2013, 12:55 pm

I was able to download it and get it working this morning, so maybe I can help.

Lordblacknail, you will actually be importing data from the petsearch excel sheet to Ryazan's workbook, rather than the other way around.

Talaan, when you open Ryazan's workbook, you need to enable macros to get it to work. They were disabled by default, but there was a bar at the top of the sheet with "Options" that allowed me to enable them. After that, click on the "Import Pets" button to get started. You need to have Office 2007 or newer for the workbook to work.

WarcraftPets Profile: Theraisama · Post by **Theraisama** » August 14th, 2014, 2:40 am

Hi there! This looks awesome, however is there a 64bit version? It's not wanting to run on Office 2013 64bit. ; ;

WarcraftPets Profile: Jaladar · Post by **Jaladar** » August 14th, 2014, 7:17 am

I am having the 64 bit issue too....this is the error I am getting:

Compile Error: The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with PtrSafe attribute.

I have no clue what all that means but I am guessing you do I hope ? Looks great if I can get it to work

Jal

Iopos · Post by **Iopos** » August 17th, 2014, 10:10 pm

I clicked on the link to get it and got nothing but a page of random gibberish

I'd like to actually give it a try...and I have a question. Once I do get a download of it, will it work with Open Office? I know OO can open Excel files, but I don't know if this is designed to work with it

Griffin224 · Post by **Griffin224** » August 20th, 2014, 11:20 am

Hey, for those having the 64 bit issue....

When the compiler opens the code and you see THIS in red...
Private Declare Function

add PtrSafe between Declare and Function, the text should turn black, then you can save it and should be good to go!

WarcraftPets Profile: Jaladar · Post by **Jaladar** » August 26th, 2014, 9:06 am

Griffin224 wrote:Hey, for those having the 64 bit issue....

When the compiler opens the code and you see THIS in red...
Private Declare Function

add PtrSafe between Declare and Function, the text should turn black, then you can save it and should be good to go!

This worked Thanks

Now my next question...

Under the "Basic Info" section, numerous pets are highlighted in red...what does that indicate? And Species ID..whats the difference between red and blue numbers here?

*Edit* I think I answered my own questions...Blue vs Red numbers indicate Male or Female breed (?) and the red highlighting indicates pets that there are duplicates of (?) I believe

Iopos · Post by **Iopos** » October 8th, 2014, 12:19 pm

Okay, I got it to download and got my spreadsheet from Petsear.ch, but now I can't get it to copy and paste into P.E.T. It keeps saying that what I'm trying to paste is too big and there's not enough space on the spreadsheet to paste it. I might be doing something wrong(plus the "button" for importing pets does nothing for me, just highlights one of the squares behind it so I can't access the instructions in there. Any suggestions?

Also, it does seem to work in Open Office as well, minus the button thing, and I do have macros enabled

P.E.T. - Keep your pets organized

Re: P.E.T. - Keep your pets organized

Re: P.E.T. - Keep your pets organized

Re: P.E.T. - Keep your pets organized

Re: P.E.T. - Keep your pets organized

Re: P.E.T. - Keep your pets organized

Re: P.E.T. - Keep your pets organized

Re: P.E.T. - Keep your pets organized

Re: P.E.T. - Keep your pets organized

Re: P.E.T. - Keep your pets organized

Re: P.E.T. - Keep your pets organized

Re: P.E.T. - Keep your pets organized

Re: P.E.T. - Keep your pets organized

Re: P.E.T. - Keep your pets organized

Re: P.E.T. - Keep your pets organized

Re: P.E.T. - Keep your pets organized

Re: P.E.T. - Keep your pets organized