|
|
Damage spreadsheet tip...
Server: Fenrir
Game: FFXI
Posts: 183
By Fenrir.Brimstonefox 2013-07-22 09:17:59
For anyone using Motenten's damge spreadsheets, I came up with a VBA macro to scroll through the lists to find gear that might match certain criteria.
I modified this DRG sheet with several macros for those that like examples:
http://www.gamefront.com/files/23542842/DPS+Calculator+-+Drg+Marcos.7z
You can just enable macros then go to View > Macros (on far right) > Run (at least for Excel 2010, not sure exactly where it is on other versions).
This sweeps through the various lists for the 2nd set of gear. (only does grips for the TP gear since that can't change for WS/Jumps) it does not do weapons at all.
Some random notes:
The gear it picks is not 'optimal' the algorithm itself basically dumb and just tries every piece of gear in every slot, it does not try every possible combination, so you can get different answers not only depending on buffs/mob, but also based on what gear you started with, thusly it will probably overlook stuff with set bonuses entirely unless they're in as the defaults.
How fast it goes will be a function of the excel version and/or computer, should only take a min. or two to run a macro though
If the validation lists are not setup correctly it will crash (chances are picking that piece of gear without running the macro will give the sheet problems too)
If you insert new gear onto the gear tab, it should continue to work fine assuming the new gear is in the validation list
It may do stuff like pick 2 Rajas Rings or Brutal earrings, that will have to be corrected manually.
I created a lot of macros for the TP sets because the algorithm is not smart, the basic idea would be you could run MaxAccTP then MaxDPSTP or run MaxAttTP then MaxDPSTP (i'd expect different answers since the early pieces (grip ammo head neck) would offset the abundance of acc or att. on later pieces (legs, feet). Even running MaxDPSTP 2x in a row may give different results. I still recommend tinkering with the results, but these will probably find some combinations you would not normally think of (and can rebase a whole gear set pretty quickly)
The code itself is here, I've put some comments to help explain it, if people have suggestions for improvement please post them.
Code Sub MaxDPSTP()
Dim i As Long
Dim max As Double
Dim total As Double
Dim save As Range
Dim rng As Range
Dim cell As Range
Dim x As Long
max = 0
'this should go down a gear column (rows 4 though 17 are sub through feet slots)
For x = 4 To 17
total = 0
'column identified here along with worksheet, this is the gear that is changing (x,24 represents Column "X")
Set rng = Evaluate(Worksheets("Gear").Cells(x, 24).Validation.Formula1)
For Each cell In rng
'this line inserts the piece of gear into the cell
Worksheets("Gear").Cells(x, 24).Value = cell.Value
'this line checks the result, should vary by function (attack, acc., Str, DPS etc..) X47 is gear set 2 DPS
total = Worksheets("Gear").Range("X47").Value
If total >= max Then
max = total
'saves the high value from the list
Set save = cell
End If
'replaces the saved value as a final
Worksheets("Gear").Cells(x, 24).Value = save.Value
Next
Next x
End Sub
For anyone using Motenten's damge spreadsheets, I came up with a VBA macro to scroll through the lists to find gear that might match certain criteria.
I modified this DRG sheet with several macros for those that like examples:
http://www.gamefront.com/files/23542842/DPS+Calculator+-+Drg+Marcos.7z
You can just enable macros then go to View > Macros (on far right) > Run (at least for Excel 2010, not sure exactly where it is on other versions).
This sweeps through the various lists for the 2nd set of gear. (only does grips for the TP gear since that can't change for WS/Jumps) it does not do weapons at all.
Some random notes:
The gear it picks is not 'optimal' the algorithm itself basically dumb and just tries every piece of gear in every slot, it does not try every possible combination, so you can get different answers not only depending on buffs/mob, but also based on what gear you started with, thusly it will probably overlook stuff with set bonuses entirely unless they're in as the defaults.
How fast it goes will be a function of the excel version and/or computer, should only take a min. or two to run a macro though
If the validation lists are not setup correctly it will crash (chances are picking that piece of gear without running the macro will give the sheet problems too)
If you insert new gear onto the gear tab, it should continue to work fine assuming the new gear is in the validation list
It may do stuff like pick 2 Rajas Rings or Brutal earrings, that will have to be corrected manually.
I created a lot of macros for the TP sets because the algorithm is not smart, the basic idea would be you could run MaxAccTP then MaxDPSTP or run MaxAttTP then MaxDPSTP (i'd expect different answers since the early pieces (grip ammo head neck) would offset the abundance of acc or att. on later pieces (legs, feet). Even running MaxDPSTP 2x in a row may give different results. I still recommend tinkering with the results, but these will probably find some combinations you would not normally think of (and can rebase a whole gear set pretty quickly)
The code itself is here, I've put some comments to help explain it, if people have suggestions for improvement please post them.
Code Sub MaxDPSTP()
Dim i As Long
Dim max As Double
Dim total As Double
Dim save As Range
Dim rng As Range
Dim cell As Range
Dim x As Long
max = 0
'this should go down a gear column (rows 4 though 17 are sub through feet slots)
For x = 4 To 17
total = 0
'column identified here along with worksheet, this is the gear that is changing (x,24 represents Column "X")
Set rng = Evaluate(Worksheets("Gear").Cells(x, 24).Validation.Formula1)
For Each cell In rng
'this line inserts the piece of gear into the cell
Worksheets("Gear").Cells(x, 24).Value = cell.Value
'this line checks the result, should vary by function (attack, acc., Str, DPS etc..) X47 is gear set 2 DPS
total = Worksheets("Gear").Range("X47").Value
If total >= max Then
max = total
'saves the high value from the list
Set save = cell
End If
'replaces the saved value as a final
Worksheets("Gear").Cells(x, 24).Value = save.Value
Next
Next x
End Sub
|
|
