topic Re: in VBA Forum

Excel, VBA, and trees

Anonymous — Fri, 03 May 2002 09:46:39 GMT

I've been working with trees in Autocad/Autolisp, evolving utilities for telecommunications design. Trying to migrate some of the work to Excel where design engineers and can manipulate the forest structures and node attributes. Enter VBA. Presently attempting to formulate an algorithm to sum up (towards the root) a tree. Got some wonderful help doing this in LISP from a Richard D. Howard a year or so ago in a post called "percolating sums up tree". I am very new to VBA (as you will see if you view attached excel file and its VBA module), and am finding this task rather daunting. My algorithm is flawed (overcounts intermediate nodes), and even if it worked it is > O(n^2) and my instincts tell me it could be done closer to nlog(n). I would greatly appreciate any help at all; comments about algorithms, suggestions about data structures, and certainly criticism about my VBA coding. If the relevance of this issue
to this newsgroup is in question, I must add that I ultimately intend to rehitch parts of this effort to AutoCad through objectDBX to facilitate interactive,
dynamic design for engineers that are familiar with Autocad, and to encourage those who aren't to become so.
Thank you for your consideration.

Re: Excel, VBA, and trees

Anonymous — Fri, 03 May 2002 09:51:37 GMT

And, yes, I suppose it would help to actually attach the file.

Re: Excel, VBA, and trees

Anonymous — Tue, 07 May 2002 06:14:26 GMT

Tom,

Have you gotten any off-line help on this yet?

--
R. Robert Bell, MCSE

href="http://www.AcadX.com">www.AcadX.com

style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

"Tom_Brabant" <
href="mailto:tom_brabant_jr_@hotmail.com">tom_brabant_jr_@hotmail.com>
wrote in message
href="news:f0da241.-1@WebX.maYIadrTaRb">news:f0da241.-1@WebX.maYIadrTaRb...
I've
been working with trees in Autocad/Autolisp, evolving utilities for
telecommunications design. Trying to migrate some of the work to Excel where
design engineers and can manipulate the forest structures and node attributes.
Enter VBA. Presently attempting to formulate an algorithm to sum up (towards
the root) a tree. Got some wonderful help doing this in LISP from a Richard D.
Howard a year or so ago in a post called "percolating sums up tree". I am very
new to VBA (as you will see if you view attached excel file and its VBA
module), and am finding this task rather daunting. My algorithm is flawed
(overcounts intermediate nodes), and even if it worked it is > O(n^2) and
my instincts tell me it could be done closer to nlog(n). I would greatly
appreciate any help at all; comments about algorithms, suggestions about data
structures, and certainly criticism about my VBA coding. If the relevance of
this issue
to this newsgroup is in question, I must add that I ultimately
intend to rehitch parts of this effort to AutoCad through objectDBX to
facilitate interactive,
dynamic design for engineers that are familiar
with Autocad, and to encourage those who aren't to become so.
Thank you
for your consideration.

Re:

Anonymous — Tue, 07 May 2002 10:15:11 GMT

No, Robert. But I think I've distilled a better way to ask the question. Let the global array tree(j) of type integer contain a tree in the following fashion. Each node of the tree has an index j and each tree(j) contains the parent of j. Let the root be its own parent; tree()=. Let a second global array descendents(j) of type integer be the same size as the tree array. Compose a function such that upon application to tree, will have the side effect of populating each descendents(j) with the total number of descendents of node j, including j as a descendent of itself.

Re:

Anonymous — Tue, 07 May 2002 10:20:49 GMT

For instance if tree(1)=1, tree(2)=1, tree(3)=2, tree(4)=2, then
descendents(1)=4, descendents(2)=3, descendents(3)=1, descendents(4)=1

Re:

Anonymous — Tue, 07 May 2002 11:03:44 GMT

So do you still need help with code? (I "got" your example,
after serious brow furrowing...)

--
R. Robert Bell, MCSE

href="http://www.AcadX.com">www.AcadX.com

style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

"Tom_Brabant" <
href="mailto:tom_brabant_jr_@hotmail.com">tom_brabant_jr_@hotmail.com>
wrote in message
href="news:f0da226.3@WebX.maYIadrTaRb">news:f0da226.3@WebX.maYIadrTaRb...
For
instance if tree(1)=1, tree(2)=1, tree(3)=2, tree(4)=2, then

descendents(1)=4, descendents(2)=3, descendents(3)=1,
descendents(4)=1

Re:

Anonymous — Tue, 07 May 2002 11:43:26 GMT

Yes. Would welcome any suggestions. My mind is in a rut (it's trying to use mapcar and process linked lists).