VBA - Tree visualisation. Possibly the hardest algo logic ever to code

I've been stuck here for hours trying to come up with a VBA excel logic for this problem. Think of this as a tree problem, in which the number of branches/nodes is determined by the user and inputted at the start.

For every node, there is 3 possible outcomes, staying flat, increasing by 1% or decreasing by 1%.

I thought about it and faced the following problems, was wondering if this makes sense or if its even doable:

1) The row insertion to build a "tree" is computationally expensive, no way around it. While this is fine for a small number of nodes, a bigger tree would just take hours for a click of the macro! ( i intend to use this up to 11/12 nodes, which takes way too long!)
- Is it possible to build a solution where macro calculations uses the same "tree" once it has been generated once, rather than to regenerate the tree every single click. i.e. to spilt up the processes, i would run a tree with 12 node at the start. And afterwhich the tweaking of the input values simply generates values that shows itself up in the tree, rather than cleaning the sheets and inserting new rows again.

2) Let me illustrate whats going on here in the best way i can.
The initial value of 100, which was used for this test, will be replaced by a formula. Lets call this formula, (A*B*Sqrt(C) + 6/X), where A, B, are constants and X is the initial % move input in the sheet which the tree branches are based off. C is the tricky bit - this is assumed to be constant until a manual intervention comes in.

Lets for a moment assume a simple tree model, with 5 nodes - giving the following structure. 1, 3, 9, 27, 81 ,243 ... 3^n, where n is the number of nodes.
Running this formula with an initial input of 2%, whereby X = 2%.
We get a nice small tree with values at this point of time.
I am trying to build a more dynamic tree here, whereby i can step in for e.g. on the 2nd value of node 3, adjust the input for C and see how the dependant nodes changes. All without affecting the previously assumed C value on nodes 1 & 2, and nodes not directly affected by the forward path of this particular value.

So in this picture below, only the blue paths should be affected by the new C value input. Is this something possible? I am so stuck...

Attachment 530711

It's not clear for me what you want to use it for. But let's make fist step. Sheet 1:
A2: starting value, B2: levels, sheet2 output

Please Login or Register  to view this content.

Thank you for the very prompt help Kaper. Thats a genius method.

To answer your question on the purpose: I am trying to model a population birth rate in a city where after every year, T+0 to T+n, where n is the number of nodes. So every +1 year, there could either be 101% of the population, 100% or 99% of the population remaining in the city.

To add to the complexity, the number of people in the city has to be dynamic at any time. So for example we know the path in the 5th year where we get +1% of population every year. so at 5th year we should have (1.01)^5 people. However, since this city is dynamic, there could be people leaving or entering the city, so manual adjustments to the city population has be to catered for.

E.G. At 5th year, 5000 left the city to another place. So the spreadsheet has to be dynamic enough to adjust the 5th level node from (1.01)^5 to (1.01)^5 - 5000. And the 6th node carries on from there.... branching out again.

Not sure if i explained this clearly enough. But this seems to be really hard to code with my amateur vba skills. Is this possible?

Also, the spreadsheet seems to crash when i simulate 10 levels and above

I'll check

You mention that the spreadsheet crashes.
What is the error message or does it just say "not responding" ?

This is not a problem it is a 'feature' with the newer versions of Excel.
I fell into the same 'trap', but... just let it run and it will finish.

I modified the file Kaper attached and added a statusbar update and you can see it working, I also noticed that if you add a Doevents in the for next loop it generally keeps on going.
I made it levels 10 and it works

I place a button on Sheet 1

That looks lovely - the status bar update. Very handy indeed.

However, what about the 2nd part to the question? Is that something doable?
I am referring to : "To add to the complexity, the number of people in the city has to be dynamic at any time. So for example we know the path in the 5th year where we get +1% of population every year. so at 5th year we should have (1.01)^5 people. However, since this city is dynamic, there could be people leaving or entering the city, so manual adjustments to the city population has be to catered for.

E.G. At 5th year, 5000 left the city to another place. So the spreadsheet has to be dynamic enough to adjust the 5th level node from (1.01)^5 to (1.01)^5 - 5000. And the 6th node carries on from there.... branching out again."

Meaning, is there a way to be able to tweak one of the level input at any given time, to manually change the input, say, of a certain point on the 3th level.And to calculate the other 4th, 5th, 6th, etc based on that dynamic manual change... Not sure if this is clear?

I just looked at the fact if there was an error, and that is not so.
About the modifications, well I guess you're the one with the 'statistical' knowledge, I'm just somebody that can code
I guess you're the best one to modify entry points and use these a reference?

I you can add some sample input or what you think you need I'll see if I understand it.

May be a good idea would be to have not values but formulas. Then you could manually change selected node/nodes and all dependant nodes would change accordingly.

See sample file.

@Keebellah - Glad to see you joined thread giving some valuable input. I changed a bit the statusbar message.
Now it is more linear, and do not refer to cells (it's time consuming, and the code is anyway already quite slow, because it uses rows inserting).

edit: and again @songhaegyo - have you noticed change in post #9 and a comment from moderator:

Last edited by AliGW; Today at 01:30 PM. Reason: Unnecessary quotation removed.

Please follow AliGW advice (and our rule 12 of https://www.excelforum.com/forum-rul...rum-rules.html )
By the way - this part of thread title is also a bad idea: "Possibly the hardest algo logic ever to code" forum search is based mostly on thread titles and while mentioning in a title "tree visualisation" is a good idea, comments on possible complication are not. While it's not written explicite in Point 1 of the rules, it fits the idea behind.

I've seen and read your PM but this seems way over my head, let me read it carefully and see if I can come up with something.
I do have a way that I think will wok to halt the calculations and resume at another moment but am not sure it I can implement here

Note to all participants: please keep the entire conversation here within the thread.

Remember, this is a public forum and all information needs remain public. This thread will be of little use to anyone in the future wishing to use the methods discussed here if crucial bits of information are missing.

Thank you for your co-operation and understanding.

You shouldn't really do that - just bump the thread with the information, as you did. Helpers will respond when they are ready, not 'on demand'.

True, that is why I answered here an not in the PM

Yes, I realise that - thank you.

Please be patient. This is not a paid 'service': people help here on a voluntary basis and of their own goodwill, and are located in many different time zones.

Just what I was about to say too ...

This seemed a litte faster :

Please Login or Register  to view this content.