Re: Grid and Array Association

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Webbiz <nospam@xxxxxxxxxxxxxxxx>'s wild thoughts were
released on Mon, 22 Jun 2009 13:38:19 -0500 bearing the
following fruit:

On Mon, 22 Jun 2009 10:14:27 +0100, Jan Hyde
<StellaDrinker@xxxxxxxxxxxxxxxxxxx> wrote:

Webbiz <nospam@xxxxxxxxxxxxxxxx>'s wild thoughts were
released on Sat, 20 Jun 2009 22:56:11 -0500 bearing the
following fruit:

On Fri, 19 Jun 2009 20:57:07 -0500, Webbiz <nospam@xxxxxxxxxxxxxxxx>
wrote:

On Fri, 19 Jun 2009 12:28:52 -0500, Webbiz <nospam@xxxxxxxxxxxxxxxx>
wrote:

On Fri, 19 Jun 2009 10:23:39 +0100, Jan Hyde
<StellaDrinker@xxxxxxxxxxxxxxxxxxx> wrote:

Webbiz <nospam@xxxxxxxxxxxxxxxx>'s wild thoughts were
released on Thu, 18 Jun 2009 21:45:32 -0500 bearing the
following fruit:

Wasn't sure how to name this subject. :-b


Maybe if you post some test data it would help. I'm still
not clear on what it is you are trying to achive.

J

Say you have a MSFlexGrid1 with 40 columns.

Say that the header (row 0) contains a date that labels what that
column is for. The dates are sequential, except weekends are skipped.
Thus, we're talking week days only.

Say that you are performing a bunch of calculations that returns
dates. Each time a date repeats, you increment a counter for that
date.

When all the calculations are completed, you want to put the total
count for each date in the cell just below that date (row 1).



Variables
-----------

dFirstDate 'This is column 0 date
dLastDate 'This is column 39 date


Below is what I'm thinking as the way to do this

Type DATECNT
dDate as Date
lngCnt as long
End Type

Dim GridArray(0 to 39) as DATECNT

Loop through GridArray assigning the date found at the top of the grid
to GridArray().dDate.

For each date returned from the calculations, loop through
GridArray().dDate till you find that date, then increment
GridArray().lngCnt.

When all the calculations are done, transfer the counts to the
respective cell in the grid.

For x = 0 to 39
MSFlexGrid1.Row = 1
MSFlexGrid1.Col = x
MSFlexGrid1.Text = GridArray(x).lngCnt
Next x

This seems like a lot of looping, especially having to loop through
GridArray() to find the date that matches the one returned from the
calculations.

Is there a more efficient way to accomplish this?

Thanks!

Webbiz




You have a bunch of calendar dates.

5/1/2009
5/1/2009
5/1/2009
5/4/2009
5/5/2009
5/6/2009
5/6/2009
5/6/2009
5/6/2009
5/6/2009
5/6/2009
5/7/2009
5/7/2009
5/8/2009
5/11/2009
5/12/2009
5/12/2009
5/12/2009
5/12/2009
...

etc. etc.

All are weekdays only.

Now, you have a grid of 40 columns. MSFlexgrid1.

At the top of this grid you have the dates 5/1/2009, 5/4/2009,
5/5/2009... (no weekends) to fill up all the columns.

The dates above need to be counted and the totals placed in the proper
column.

So for column 0 (5/1/2009), you would have the value 3 in row 1.
For column 1 (5/4/2009), you wuld have the value 1 in row 1.
For column 2 (5/5/2009), you would have the value 1 in row 1.
For column 3 (5/6/2009), you would have the value 6 in row 1.

And so forth.

The data (dates) you see above come one at a time within a variable
called dResult.

Now...

What I'm trying to figure out is the most efficient way to do this.

Keep in mind that the data (dates) do not come to you in the order I
typed them. So you just can't start counting them consecutively.

The program runs calculations within a loop and spits out a date. It
could spit out the following:

dResult = 5/4/2009
dResult = 9/9/2010
dResult = 1/3/2003
dResult = 7/18/2005

etc. etc.

I must look at dResult, determine whether it falls within the date of
Column 0 and Column 39, then increment the counter that represents
that date if it does. So when all the calculations are done, I have a
grid that shows how many times those dates popped up from the
calculations. Any date that falls outside those dates listed in the
Grid are not considered at all.

Hope that explains it better. :-)

Thank you.

Webbiz



What do you think of this idea to accomplish efficiently what I'm
trying to do?

1. Reduce each down to a value low enough to use as an array index.
For example, since the grid will hold 40 dates from dStartDate to
dEndDate (minus weekends), take the DateValue of the first date and
use this value to subtract from every date result.

lngDVDate = DateValue(dStartDate)

dResult = BunchOfCalcs 'returns a date

lngResult = DateValue(dResult)

'Make sure dResult falls within dStartDate and dEndDate
If lngResult > lngDVDate - 1 or lngResult < DateValue(dEndDate) + 1
then

GridArray(lngResult - lngDVDate) = GridArray(lngResult - lngDVDate)
+ 1 'increment the count

This way, whenever a date falls within dStartDate to dEndDate, simply
using the DateValue of the date minus the DateValue of the first date
would create the array index value.

2. Now comes the part where I have to transfer the results to the grid
itself. What I think I can do here is to LOOP through the grid from
Column 0 to Column 39, and use the DateValue of the Row 0 date to
index the array and get the correct value.



For x = 0 to 39

With MSFlexGrid1
.Row = 0
.Col = x
lngDateCnt = GridArray(DateValue(.Text) - lngDVDate)
.Row = 1
.Text = lngDateCnt
Wend

Next x


Whaddaya think?

Webbiz


I found this information doing a search:

"There are numerous ways to get data into the MSFlexGrid. There is no
"officially" preferred method or clear speed differences, so the
proper one to use depends on the source of the data and personal
taste."

"If you don't want the current cell to change when you load data as it
does with the Text property, you should use the TextArray or
TextMatrix property. These properties set the contents of an arbitrary
cell without changing the Row or Col properties. TextMatrix addresses
a cell using the familiar row and column notation. TextArray uses an
index to identify a cell. The index is simply a zero-based enumeration
of the cells from left to right, top to bottom. In a 2 x 2 grid the
following lines both address the lower right-hand cell:

MSFlexGrid1.TextArray(3) = "X"
MSFlexGrid1.TextMatrix(1,1) = "X"

This is a step in the right direction. Rather than my setting the row
and the col separately as in my previous example, I can simply
reference the cell directly using .TextMatrix.

Or I could use .TextArray, where my indexing would start at 40 for
Column 0 row 1. Since this article stays speed is not the issue,
.TextMatrix is perhaps easier to use in this case.

So perhaps I could do the following?

For x = 0 to 39

MSFlexGrid1 .TextMatrix(1, x) = GridArray(DateValue(.Text) -
lngDVDate)

Next x

Anyone have any comments as to whether I'm working in the right
direction? Would appreciate comments from the pros.





I like textmatrix because I know exactly where the data is
going and it is clear to anyone reading the code.

Your data is in date order so I'd probably just loop through
the data, counting as I go.

Jan, you didn't read my comments following that data. Here is what I
said:

Keep in mind that the data (dates) do not come to you in the order I
typed them. So you just can't start counting them consecutively.

The dates are thrown at your randomly.

Then I'd either still loop through, or if there are a lot of
dates, sort them first.

Where is the data coming from?

J

--
Jan Hyde
.

Relevant Pages

  • Re: Grid and Array Association
    ... When all the calculations are completed, you want to put the total ... Loop through GridArray assigning the date found at the top of the grid ... called dResult. ...
    (microsoft.public.vb.general.discussion)
  • Re: Grid and Array Association
    ... When all the calculations are completed, you want to put the total ... count for each date in the cell just below that date. ... Loop through GridArray assigning the date found at the top of the grid ... called dResult. ...
    (microsoft.public.vb.general.discussion)
  • Re: Grid and Array Association
    ... When all the calculations are completed, you want to put the total ... count for each date in the cell just below that date. ... Loop through GridArray assigning the date found at the top of the grid ... called dResult. ...
    (microsoft.public.vb.general.discussion)
  • Re: Grid and Array Association
    ... When all the calculations are completed, you want to put the total ... count for each date in the cell just below that date. ... Loop through GridArray assigning the date found at the top of the grid ... called dResult. ...
    (microsoft.public.vb.general.discussion)
  • Re: VBA: For Count, when count changes from cell to cell
    ... use since it will do its calculations in real-time without you having to run ... I have a question about UDF. ... Dim LC As Integer ... means that you can refer to it in a cell just like a built-in Excel function. ...
    (microsoft.public.excel.misc)