Help me make sense of excel and VB

June 5, 2009 8:14 AM

I want to take a small worksheet I created in excel and turn it into a standalone application. How?

I've made a pretty simple excel sheet budget that calculates values for certain criteria and shows totals for 10 years for various budgeting cases.

I want to make this into an application using VB so that a someone can execute it, type in values into fields, rather than cells, and so that a chart output is displayed given the totals when a user submits all the values.

Anyone want to help me figure out where to start? I'm thinking I should use visual basic since that is the only thing I am (a little) familiar with.

Help mefi geniuses!

posted by icarus to computers & internet (5 comments total)

I don't have the specific answer, however, you may want to post this type of programming question here.
posted by dfriedman at 8:20 AM on June 5

Eh, it's a middling-difficult problem. Part of the problem is that Office VBA is still VB-6-based. VB.NET has moved on.

You want to export your .bas modules from your XLS.
Create a new VB 6 project.
Import your .bas modules.
Link MSSheet and MSChart.
Create a new VB form with a couple of fields for data entry. Create a Go button.
When the Go/Submit button is pressed, populate your data on a hidden MSSheet.
Create a new MSChart based off the Sheet data.
Display the MSChart.
Don't forget an easter egg on the About screen that says "THANKS UNIXRAT!!!" and make it blink.
posted by unixrat at 8:27 AM on June 5

How come you don't you just do it vanilla Excel? You might be surprised on how sexy some interfaces/worksheets can be. Gantt chart templates and other things spring to mind.

If you must do it programatically I suggest you use VBA, within Excel. I wouldn't go to VB if charts are involved. (And if charts--and by 'charts' I'm assuming you mean 'graphical charts'--weren't involved, and it was to be a standalone app, I'd skip the Mickeysoft altogether and write it in HTML/JavaScript.)

Here's a quickie on VBA popup dialogs. Maybe put a button on the spreadsheet that pops up the dialog, then once the user enters the values and clicks "OK," apply the numbers in the (text boxes | combo boxes | list boxes | checkboxes) entered into the sheet (which is pre-greased to accept the values in surreptitiously hidden rows and columns) and have a pre-fab chart driven by said numbers? Hmmmmm?
posted by ostranenie at 10:27 AM on June 5

If it's something you could re-implement in Microsoft Access 2007, Microsoft has a freely redistributable runtime for that, which allows you to create stand-alone apps. I also like ostranenie's solution of keeping it in Excel and just using some VBA to make it more application-ish.
posted by wheat at 12:59 PM on June 5

If you want the world at large to use this, write the interface in HTML and the logic in javascript.
posted by PueExMachina at 9:36 PM on June 5