Aldriana's Combat and Mutilate Sheet Companion - Page 3

dinesh · 02/12/10, 3:29 PM

Version 3.3.6: Download Ald Combat and Mutilate Companion_3.3.6.xls

* Added Lich King loot
* Updated to be compatible with Mutilate 1.3.3 and Combat 1.5.1 sheets
* Updated Toravon gear data, marked Heroic Sanctified items as Heroic/Hardmode

A note on why I need to release a new version almost every time the Mutilate or Combat sheets are updated: basically, it's for one thing, the Estimated DPS calculation. It's listed at the top of the mutilate and combat sheets, and if you click in that cell you'll see it's a reference to the respective Calculation sheet. Unfortunately, the row number for this calculation often changes from sheet to sheet. Updating the row number is something you have to then do in MY companion sheet, so that we can go to the right place to get the DPS.

If you don't want to wait for me to release an update, you can try making the changes yourself. Basically, you'll need to open up the Visual Basic/VBA for the sheet, and open up module1. At the top of this you should see a lot of constants defined. Find the constant for CombatDPSCellRow or MutiDPSCellRow and update them to the correct new row that is referenced from the main page. Now save, and hopefully you'll be all set! The easiest way to test is to hit the "Copy" Saved DPS from the options page and make sure it grabs DPS correctly from the combat and/or mutilate sheet, then clear it again when you're done.

Furtim · 02/13/10, 2:38 AM

Originally Posted by dinesh

A note on why I need to release a new version almost every time the Mutilate or Combat sheets are updated: basically, it's for one thing, the Estimated DPS calculation. It's listed at the top of the mutilate and combat sheets, and if you click in that cell you'll see it's a reference to the respective Calculation sheet. Unfortunately, the row number for this calculation often changes from sheet to sheet. Updating the row number is something you have to then do in MY companion sheet, so that we can go to the right place to get the DPS.

Application.WorksheetFunction.Match("Total Damage", Excel.Workbooks(BookName).Worksheets(CalcSheetName).Range("A:A"), 0)

Use this code in your Companion sheet to make it future proof via having it search the relevant Calcs sheet for "Total Damage" and return it's row number, provided Aldriana doesn't change the wording in future versions of his sheets.

You'll have to rework some of your code to implement it, but I ran several tests on my end and it correctly returns the row which holds the total DPS value.

Here's an example of it being used for copying the DPS:

Sub CopyDPS()
    If Worksheets("Gear Upgrades").Spec.Value = "Combat" Then
        BookName = Worksheets("Options").Cells(3, 3)
        DPSCellRow = Application.WorksheetFunction.Match("Total Damage", Excel.Workbooks(BookName).Worksheets(CalcSheetName).Range("A:A"), 0)
        Worksheets("Options").Cells(8, 3).Value = Application.Round(Excel.Workbooks(BookName).Worksheets(CalcSheetName).Cells(DPSCellRow, CombatDPSCellColumn), 2)
    ElseIf Worksheets("Gear Upgrades").Spec.Value = "Mutilate" Then
        BookName = Worksheets("Options").Cells(4, 3)
        DPSCellRow = Application.WorksheetFunction.Match("Total Damage", Excel.Workbooks(BookName).Worksheets(CalcSheetName).Range("A:A"), 0)
        Worksheets("Options").Cells(8, 3).Value = Application.Round(Excel.Workbooks(BookName).Worksheets(CalcSheetName).Cells(DPSCellRow, MutiDPSCellColumn), 2)
    End If
End Sub

dinesh · 02/13/10, 4:41 AM

Hmm, I was hoping to convince Aldriana to use a defined name for the cell, but I guess it's not up there on his to do list, so I suppose I can use a string match instead.

• Aldriana · 02/13/10, 4:43 AM

Dare I point out that Aldriana *did* add a defined name for the cell in Combat 1.5.1? I mean, I admit I forgot to do so for Mutilate and should probably fix that at some point, but, uh... it is at least in Combat.

Ekot · 02/13/10, 1:39 PM

I notice that when my gear setup is touching crit cap, my crit for enviserate is about 70%. I wonder if it is a better idea to replace enviserate glyph with something else? Spreatsheet is showing a slight decrease in dps. Do I really need 70% crit for that?

dinesh · 02/13/10, 1:45 PM

Originally Posted by Aldriana

Dare I point out that Aldriana *did* add a defined name for the cell in Combat 1.5.1? I mean, I admit I forgot to do so for Mutilate and should probably fix that at some point, but, uh... it is at least in Combat.

Heh, you daren't! Thanks, I appreciate it.

I must have checked the Mutilate sheet first, didn't see it, and assumed it just didn't make the cut this time around. Much obliged.

Originally Posted by Ekot

I notice that when my gear setup is touching crit cap...

You probably want to ask this in the actual spreadsheet thread, or one of the mechanics threads, not here in the companion sheet thread.