Where to develop this tool: Excel VBA vs VB

I post this topic in the Excel VBA forum, since I expect that the people here would know more about the advantages/disadvantages of Excel VBA

Here is the deal: I am accustomed to developing tools in Excel VBA, since most of the time it would involve tables of data and/or charts that need to be created. Other good reason is ofcourse the fact that it comes free ;)

So... Now I started once again to develop an excel tool, this time to manage testcases for a certain testobject. When I started I actually used Excel for quite a few purposes, but now the tool has developed over time, it is becoming more VB in Excel than actually using excel itself.

There are two important things about this tool:

1) It's GUI: I need a flexible GUI with quite a lot of features, and I am starting to get confronted with the limitations of using VBA: No menu's, treeview is missing a whole bunch of events that VB does have. Those are the two most important examples.

2) It's formula's : I implemented a smart way to deal with formula's in datacontainers (simplified: I create XML files that can contain formula's as value, which are calculated just at the moment the XML file is saved. I use the Excel functionality there: simply paste in the IF, SUM, [Whatever function] in a cell, let Excel do his math and extract the result again. Very simple but effective.

As you can see: points 1 and 2 clearly bite eachother... So my questions are:

A) Can I import GUI parts and menu's into VBA or
B) Can I use an objct of Excel that can do the calculations for me, but then in VB?
C) Any other ideas or suggestions?

First, VB 2005 Express is free too. And I think 2008 is available too (?)

In Excel, you can insert different ActiveX controls which don't come necessarily with Office. The problem is to distribute them. You would need to create a setup file to copy and register the OCX and/or DLL files your application need to run. This may bring some headaches as well as in VB...

For the menus, you can do it in Excel using API's. It's not as simple as in VB, but doable.

To use an Excel object in VB, just use Automation. You need to create variables-objects to open a session, an empty workbook... and run it hidden. When you're done, you close all these object properly so there's no process still alive.

Just to run with a couple of MPi's points:

1) the TreeView control *is* avaialbe in VBA - right-click the UserForm's toolbox and select what you want.

2) is you mean form menus, then refer to MPi's point, though there are many examples of people constructing their own using either non-standard controls, or by contriving a system using labels etc.

From the other side of the fence, you can use the MS Spreadsheet control to give you the calculation power of Excel in VB.

I knew someone would start about VB Express ;) point is that when it comes to .Net, I am oblivious and I do not have the time to learn the language (alas)

Thanks for your reply, I'll let your advice sink in and see what I can do with it :)

++edit
Missed your comment Timbo, thanks for your input (as always)

About the treeview:

The treeview is available indeed, but some events are not present - like an even which is triggered before collapsing/expanding a treeview node. Issue I have here is that I use the treeview as a representation of my XML structure, and support drag and drop functionality for the nodes. I noticed that when leaving the treeview behaviour "as is" it is easy to mess up your structure because a single mouseclick can be used to expand/collapse a node, but also mean selecting a node so I can drag it. I implemented the double click as the command used to expand / collapse nodes, but this required blocking the single click expand/collapse, which is not possible - you can only reverse the action after it has been done. It works, but now that each click potentially gives a very quick expand/collapse it just looks ugly and just opens the door to other issues.

I heard quite some times that the VB treeview control does support an event before actually expanding/collapsing a treeview node. And if it doesn't : I also read that you can prevent a form or object on the form to be refreshed which would at least prevent the user from seeing the glitches.

The MS Spreadsheet thingy sounds interesting, I'll look into it

I'm not sure I follow what you're trying to do with your treeview; have you hijacked the click event to initialize your drag/drop functionality? I assume you'e already seen the OLDStartDrag and other standard events?...

I am sorry if it is a bit unclear - Let me rephrase:

When you drag/drop, you select an item, then pause, then click the item again, leaving the mousebutton down. You then drag the item to another place where you release the mousebutton again

But: in some cases, a single mouse click (at the "you select an item" step described above) will trigger the expand/collapse event of the treeview. This means that when the user wants to drag and drop a node, it is in fact expanded/collapsed. This gave a rather buggy feel and also gave some weird results when you would click, drag and drop the nodes too fast.

So I figured: why not "disable" the single click expand/collapse: I programmed the treeview in such a way that when a single click would result in a expand/collapse, I just "undo" the action. But this looks hidious since I cannot prevent the expand/collapse action happening altogether - I just execute the expand/collapse an extra time to undo the actual expand/collapse.

Result is a treeview which functions like it should, but continously flickers since I am collapsing/expanding quite a lot.

I read quite a lot on the internet while I was solving this issue in VBA, but everywhere I looked for answer and found them, they would involve events which I never had seen in VBA. This made me think that VB has another treeview object which has more events then the VBA equivalent.

Now, a few days after my post, I decided to create my tool in VB, since it provides more options and our developers might be more familiar with it. After a while, I noticed that the treeview does not have any more events then VBA does, which puzzles me quite a bit. Am I missing something or is there no difference between both treeviews. And: which options do I have to cope with this issue?

Have you tried tweaking the 'Custom' property setting of the treeview? There is an option called "SingleSel" which appears to induce the action causing you the problems, if selected.