Help me make sense of excel and VB
June 5, 2009 8:14 AM Subscribe
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!
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!
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, 2009
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, 2009
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, 2009
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, 2009
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, 2009
posted by wheat at 12:59 PM on June 5, 2009
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, 2009
posted by PueExMachina at 9:36 PM on June 5, 2009
« Older what makes you think someone is attracted to you? | Stop Gmail auto-adding to my contacts list? Newer »
This thread is closed to new comments.
posted by dfriedman at 8:20 AM on June 5, 2009