Ice Foot
8/18/2010 3:54:00 AM
"David" <NoWhere@earthlink.net> wrote in message
news:ukBb5ynPLHA.620@TK2MSFTNGP06.phx.gbl...
> I'm dealing with an Excel worksheet which allow the user to enter
> a values into a year/date column and an amount column. If the
> user enters multiple years then I need to sum the values.
>
> I thought this might be easier in VB so what I had in mind was:
>
> 1) Create two identical structures (type) of:
>
> Type TWSInfo
> thisYr As Integer
> thisValue As Single
> End type
>
> Dim TThisWS(rowcount) as TWSInfo
> Dim TThisSum(rowcount) As TWSInfo
>
> 2) Read in each Excel row into the structure
>
> 3) Sort the structure by Year
>
> 4) Loop the structure array comparing the next "thisYr" element to the
> previous "thisYr" element, and if the years match, summing the values
>
> 5 ) Storing the Yr and summed value in the structure TThisSum
>
> ======================================
>
> Anyone have an easier / better way?
>
> Thanks
> David
>
>
>
>
>
i don't know about easier or better, but one alternative is use a
cSortedDictionary (Olaf Schmidt's dhRichClient3.dll)
use year as key, (automatically sorted),...something like
'pseudocode
Private mSumDict as cSortedDictionary
Sub StoreYearSum(year as string, sum as single)
if mSumDict is Nothing then set mSumDict = New cSortedDictionary
if mSumDict.Exists (year) then
mSumDict.Item(year) = mSumDict.Item(year) + sum
else
mSumDict.Add(year,sum)
End if
End Sub
Sub ReadTotals()
dim i as long
for i = 0 to mSumDict.count -1
debug.print "Year", mSumDict.KeyByIndex(i), "Sum",
mSumDict.ItemByIndex(i)
next i
End Sub
fwiw
mark