HeroForge Bug Tracking Page
Mantis Bug Tracker
All Projects | HeroForge | ModernForge | MonsterForge | SpellForge

Main | My View | View Issues | Change Log | Roadmap
  

View Issue Details [ Jump to Notes ] [ Issue History ] [ Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0000091HeroForgeOptimizationpublic06-09-2009 08:5106-16-2009 20:51
ReporterChristophe Kowalski 
PrioritynormalSeverityminorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOSOS Version
Product Version6.0.2.0 
Target Version6.0.2.1Fixed in Version6.0.2.1 
Summary0000091: Simplify and size down Class Abilities
DescriptionOverall, the sheet would greatly benefit from some diet. Amongst the biggest worksheets, Class Abilites (in size second to Feats) exhibits a large amount of cells formulae that repeats data.

The first issue to address is the concatenation over a long, eventually expandable, range. Attached is a bas module hosting a function (StringConcat) that does exactly this (it's pretty short and easy to read).

Having imported the module, it's first use would be to replace the whole of columns F, G and E (but E1) with only two cells:
Type in H1: =StringConcat("vbLf";B1:B5000)
Type in E1: =StringConcat(", ";D1:D5000)

Now you can delete the content of all cells in columns F, G and E (but cell E1).

The nice thing about this function is that it works in an array formula. Let's take advantage of this (don't forget to enter the array formula with CTRL-SHIFT-ENTER):
in H1: {=StringConcat("vbLf";IF(LEN(B1:B5000)>0;A1:A5000;""))}
in E1: {=StringConcat(", ";IF(LEN(B1:B5000)>0;IF(ISERROR(FIND(":";A1:A5000;1));A1:A5000;MID(A1:A5000;3;FIND(":";A1:A5000;1)-3));""))}

Now you can delete the content of all cells in columns C and D.

So we just deleted 21.145 cells with formulae (without loosing any functionality in my view.) The minimum saving in size is almost 300K (around 10% of the blank Class Abilities sheet), it runs faster, and additions become easier.
Additional InformationThe next step would be to simplify the formula in column B, to avoid repeating data. The main role of this column is to determine whether a given ability is available or not and it should stick to that more strictly.
The general format (but not uniform, and some pre-screening is definitely needed here) is = IF(CONDITION; repeat content of A; null)
This should become =CONDITION (saving a few more kBytes and µsec...)
The formula in H1 (and E1) could then be further simplified:
{=StringConcat("vbLf";IF(B1:B5000;A1:A5000;""))}

A greater benefit could be reached by doing some cleaning work on the quasi-orphan formulae that hang beyond column H. This would allow to sort the class abilities without loosing things. Perhaps this could also help Logan in his consolidation work. I could get on that if there is an interest for, but this should be the scope of another report.

The same technique could be used on other sheets as well (Feats would be a good candidate).
TagsNo tags attached.
Attached Files? file icon basTextProcs.bas (4,086 bytes) 06-09-2009 08:51

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to 0000094assignedLogan Andrews Structured description of class abilities 

-  Notes
(0000132)
Yonner (administrator)
06-09-2009 22:30

 I looked at this solution a month ago and encountered a few issues with it. I've since worked the issues out with some modifications. This redesign will be set for version 6.1
(0000161)
Yonner (administrator)
06-16-2009 20:06

 Changes:
Created new module StringFunctions
New recursive function in module StringFunctions:

Function RangeConcat(ByVal delim As String, ParamArray arr()) As String

  Dim rng As Range, x As Variant, y As Variant

  For Each x In arr
    If TypeOf x Is Range Then
      For Each rng In x.Cells
        If rng.Value <> "" Then
          RangeConcat = RangeConcat & rng.Value & delim
        End If
      Next rng
    ElseIf IsArray(x) Then
      For Each y In x
        If y <> "" Then
          RangeConcat = RangeConcat & IIf(IsArray(y), RangeConcat(delim, y), y) & delim
        End If
      Next y
    Else
      RangeConcat = RangeConcat & x & delim
    End If
  Next x
End Function

Changed cells:
'Class Abilities'!H1 changed from
  =G1
changed to:
   =rangeconcat(CHAR(10),B1:B5000)

'Class Abilities'!F:G contents deleted

'Class Abilities'!E1 changed to:
   {=rangeconcat(", ",IF(LEN(B1:B5000)>0,IF(ISERROR(FIND(":",B1:B5000,1)),B1:B5000,MID(B1:B5000,3,FIND(":",B1:B5000,1)-3)),""))}

'Class Abilities'!C:D contents deleted
(0000164)
Yonner (administrator)
06-16-2009 20:51

 Changes as per note 161

- Issue History
Date Modified Username Field Change
06-09-2009 08:51 Christophe Kowalski New Issue
06-09-2009 08:51 Christophe Kowalski File Added: basTextProcs.bas
06-09-2009 22:30 Yonner Note Added: 0000132
06-09-2009 22:30 Yonner Assigned To => Yonner
06-09-2009 22:30 Yonner Status new => acknowledged
06-09-2009 22:31 Yonner Target Version => 6.1
06-10-2009 21:41 Shawn Perry Status acknowledged => assigned
06-16-2009 20:06 Yonner Note Added: 0000161
06-16-2009 20:51 Yonner Target Version 6.1 => 6.0.2.1
06-16-2009 20:51 Yonner Note Added: 0000164
06-16-2009 20:51 Yonner Status assigned => resolved
06-16-2009 20:51 Yonner Fixed in Version => 6.0.2.1
06-16-2009 20:51 Yonner Resolution open => fixed
06-16-2009 20:51 Yonner Status resolved => closed
06-27-2009 11:23 Shawn Perry Relationship added related to 0000094

MantisBT 1.2.0[^]
HeroForge and the HeroForge Logo are Trademarks of HeroForge Software, LLC - Copyright 2008 - 2009 HeroForge Software, LLC. All rights reserved.
MantisBT - Copyright © 2000 - 2010 MantisBT Group
Powered by Mantis Bugtracker