Standalone application

[Maarten]

I've built a graphical application with VBA for excel.

Now I'd like to be able to run the application directly (the gui loads up instead of the workbook, with excel running in the background (preferable not visible).

Upto now I haven't been able to find how to do this, but I might have been using the wrong keywords in my searches.

Could anyone give me to code to accomplish this, or link me to a page/thread where this is discussed?

thanks in advance.

[Maarten]

if it's not possible, please let me know as well, then I can stop trying to get it to work

[mddawson]

You cannot run your VBA app as a separate application, but you can hide Excel by using the command: Parent.Application.Visible = False. Make sure you set this property back to 'True' before exiting your VBA program. Also, comment out this command on any software that you are in the process of debugging as Excel will be hidden if you have to halt execution of your code.

The one problem that I have experienced with this is that during any I/O operation, the form winds up behind any other application windows that are open at the time. Since this command also removes Excel from the taskbar, which makes no sense, this means that if you perform an I/O operation you will need to minimize any other open windows to find your userform or you will need to minimize any other windows before running your Excel app. I have posted on this matter and have not received any responses either confirming that others have experienced this or how to circumvent it.

[Maarten]

I was afraid that would be the case...

anyways, it's not such a big problem, as long as the application starts up as soon as someone opens the file.

at them moment I still have to start the application manually from the workbook (with alt-f11 and then the play icon).

So my next question is wheter this is possible.

-user clicks the .xls file
-the program gets executed automatically, and excel is minimized automatically
-if user closes the application, excel gets shut down automatically as well.

how would this work.

[mddawson]

In any VBA program you simply need to use the Auto_Open() subroutine to initiate the execution of a macro upon opening a workbook. In your case it would just need to contain a procedure call to the first subroutine in your VBA program. An example of this is from the project I just developed for ammonia emissions analysis for the Cooperative Extension department in conjunction with Jones-Hamilton:

Sub Auto_Open()
' Initialize the file status for Gaseous Gallus and display the splash screen.

Sheets("Main").Select
SetInitialFileStatus
frmSplashScreen.Show
End Sub

In this case when the workbook is opened, the file status is initialized—this program reads data sets from other workbooks and saves the results of analysis to a new workbook—and then the splash screen is displayed. The splash screen, which is a userform, contains code to display for a few seconds then switch to the main user interface which is another userform. There is also an Auto_Close() subroutine which executes when your program closes out. This is useful in case you have changed Excel parameters for your VBA program and need to return to defaults.

Another option is to write code in the ThisWorkbook module which exists by default. In the Visual Basic editor under the Project window, this is where all of your worksheets, forms and modules are listed in outline format, double click on ThisWorkbook to access its code. There you can write a Workbook_Open() subroutine which is code that is executed automatically when the workbook is opened. Again you would just write code in this subroutine to call your main VBA program.

[Maarten]

Thank you, you've been very helpful.

I had to put the following code in the ThisWorkBook module:

Sub Workbook_Open()
Parent.Application.Visible = False
frmGebouwGegevens.Show
End Sub

This works great, but I would like to mention that I didn't get it to run with the 'sub auto_open()' option you gave. Maybe that's because I'm using Excel 2000?

anyways, I can't get Excel to show again on closing of my program.

I've tried the following code:

Sub Auto_Close()
Parent.Application.Visible = True
End Sub

but It won't work.

I imagine this would be because of the same reason that the 'sub Auto_Open()' didn't work.