Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Jul 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    GUI -> Excel -> GUI

    I'm working on a little project where I'm looking to create a web-based graphical user interface that takes the user's inputs, connects with Microsoft Excel, uses the inputs to do some calculations, takes the Excel results, and then display outputs on the GUI. I'm not that savvy with programming, so I'm just looking for a general framework as to how I go about doing this (i.e. what languages do I use and how I implement such a thing?). Thanks in advance for any advice!

  • #2
    Regular Coder
    Join Date
    Apr 2009
    Posts
    244
    Thanks
    1
    Thanked 20 Times in 20 Posts
    Hey bryanwt3,

    1. Could you please go into more detail regarding what calculations you have. The reason I'm asking is, does Excel necessarily have to be the means by which you perform them? At a first glance, it seems that you're overcomplicating it a little, but then again I'm keeping an open mind, so I'm not necessarily claiming that you shouldn't use Excel.

    2. As far as what languages/platforms you need to work with, they can be (but not limited to):
    • Javascript (for client-side)
    • CSS (for styling)
    • Either PHP, Classic ASP, ASP.Net (for server-side)
    • You might also need to know SQL to do your calculations


    Mike

  • #3
    New to the CF scene
    Join Date
    Jul 2009
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    GUI interface request

    Mike,

    I have the same request as well. I have a detailed excel spreadsheet (model) with tabs that have worksheets performing calculations. I centralized one tab that serves as my input schedule - that is, one can input the values in the certain cells on that tab and the calculations will follow through to the other tabs.

    The reason I'm using excel is that I'm familiar with it as are the other users of my model.

    I would like it to be more user friendly - hence, the requirement or the request for a GUI interface that will allow the users to post the values on the screen that gets linked to the cells on the input page and from there drives the calculations on the other tabs in the spreadsheet.
    I understand that VBA programming may be required to do so - not overly familiar with VBA (but I'm pretty good at excel).

    Is there an easier approach or can the VBA instructions be easily followed? Or alternatively, is there GUI interface software (3rd party) that can be incorporated into excel that will allow someone to create the GUI interface?

    Your help would be greatly appreciated.

    Thanks in advance.

  • #4
    Regular Coder
    Join Date
    Apr 2009
    Posts
    244
    Thanks
    1
    Thanked 20 Times in 20 Posts
    Hi PCExcel,

    Will this spreadsheet be located only on one machine?

    Mike

  • #5
    New to the CF scene
    Join Date
    Jul 2009
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Mike,

    Yes - it will be as I will be the owner of the spreadsheet. However, I will send it out to the various users to allow them to input the values as they wish to perform sensitivity analyses.

  • #6
    Regular Coder
    Join Date
    Apr 2009
    Posts
    244
    Thanks
    1
    Thanked 20 Times in 20 Posts
    Hi PCExcel,

    You can certainly use VBA to build up a form, where you will enter values which will then interact with sheets/cells. I think VBA is not difficult to learn, especially if you are familiar with the Excel environment. One of the ways you can learn is by recording a macro and then viewing the resulting code. It's pretty straight forward. You'll begin to understand once you see it.

    Having said that, I personally would not use VBA in your situation. I would either build a Web page, or a Windows Application that would intract with a database server that would hold all the data, rather then an excel file. Now, there are a million reasons I have for this, and unfortunately I don't have the time to explain those (my apology).

    Don't rule out the VBA approach completly(I'm just one person with an opinion), but if you're considering my approach, then you'll either have to learn the languages I posted above, or you'll have to pay somebody to write the GUI for you.

    Regards,
    Mike

  • #7
    New to the CF scene
    Join Date
    Jul 2009
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Mike,

    Thanks for the response. In response to your suggestions:

    1. Recording a macro - not sure how that process will work in creating a GUI interface.

    2. Applications - are there any 3rd party software that has a easy to follow wizard menu that help one create a GUI interface without going through the time consuming exercise of learning a language (whether it is VBA for above or web based application).

    Please let me know.

    Thanks again.

  • #8
    Regular Coder
    Join Date
    Apr 2009
    Posts
    244
    Thanks
    1
    Thanked 20 Times in 20 Posts
    Hi PCExcel,

    I mentioned recording an Excel macro because it is one way of getting familiarized with VBA. As far as how exactly you create the GUI using VBA, IF you decide to go with this route yourself, you will have no choice but to spend the time and to learn how to do it, there is no other way around it. Again, that's not to say that you won't pick it up. I think once you get into it, you will begin to understand, especially with the fact that you know Excel well. Anyway, follow these steps to create a sample GUI that will interact with cells. Basically it's a textbox and a button. When you click on the button, whatever is in the textbox goes in cell "A1" of sheet "Sheet1".

    1. Open a fresh Excel workbook. Press ALT-F11 to go into the VBA Project window.
    2. Once there, create a new form by right-clicking on VBAProject->Insert->UserForm. A toolbox should come up exposing various controls that you can put on it. If it doesn't come up, you can open it from the menu on top.
    3. Drag a textbox and a button onto the form.
    4. Double-click on the button, and it should automatically create the click handler "CommandButton1_Click()" for it. Make this event handler look like this:
    Code:
    	Private Sub CommandButton1_Click()
        		Sheets("Sheet1").Cells(1, 1).Value = TextBox1.Value
    	End Sub
    5. Now insert a new module by right-clicking on VBAProject->Insert->Module. Module1 will be created
    6. Inside it, put the following code:
    Code:
    	Sub auto_open()
        		Load UserForm1
        		UserForm1.Show
    	End Sub
    7. Save your file. When you open it, just enable macro. Your form should now come up.

    So, that's that for VBA. It's just a small example to get you started. As far as third party solutions, sorry, I don't know any. However I have one comment about it. If there is a solution like that, chances are that you'll have to pay for it, and if you're willing to spend money, you might as well pay somebody to build a custom solution for you, which might actually be cheaper and better overall.

    Regards,
    Mike

  • Users who have thanked Mike_O for this post:

    PCExcel (07-18-2009)

  • #9
    New to the CF scene
    Join Date
    Jul 2009
    Posts
    4
    Thanks
    1
    Thanked 0 Times in 0 Posts

    GUI interface request

    Mike,

    Thank you for the follow-up. I will try your VBA suggestion.

    Thanks again.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •