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.

[mddawson]

Thank you, you've been very helpful.

No problem. I try to help where I can as I know the frustration of asking for help on a forum to deaf ears. (I have not received any assistance on all but one of my inquiries since joining here.) The problem is that this forum is only divided into a couple a categories and the Excel Programming section covers a very broad spectrum (e.g., basic programming, input/output, charting, worksheet management, etc). Perhaps we should suggest that the site moderators create more sub-categories where needed as for now when someone posts their inquiry drops off the first page within an hour or so unless people immediately begin responding.

Anyway, you may need to make Excel visible in a Workbook_BeforeClose() subroutine in your ThisWorkbook module since it is made invisible in the same manner.

Sub Workbook_BeforeClose()

Parent.Application.Visible=True
End Sub

Excel 2000 should have no problem running an Auto_Open() or Auto_Close() subroutine beyond VBA being quirky. I wrote my first full blown Excel app a little over year ago for a class assignment and as I was an undergrad at the time and did not have my own office so I used the PCs in the computing sites throughout campus which had either Office 2000, 2002 or 2003 depending on where I was. That program consistently worked on every version of Excel as expected though it was not as complicated as the project I am now working on.

I have noticed many issues with the project I am working on with some of these more esoteric VBA features. The most persistent has been the sudden appearance of the Toolbox palette when I execute my program now. This palette contains the widgets for userforms and should only be available when I am in design mode in the Visual Basic Editor. Since about version 0.4.11 of my project (now at 0.4.14), it now appears on screen during execution of the program when it switches from the splash screen to the main user interface. I have no idea what caused it appear and I have received no responses to my post on the matter on this forum.

It also seems that you may be experiencing something that I have come across as this project progresses. There are times when it seems like code within my program is not being executed. For instance, I disable the standard menu items on the menu bar and hide all toolbars when my program is running; at the beginning of execution the visibility state of numerous standard toolbars are stored before they are deactivated so that only those toolbars that the user had active before running the program are redisplayed when the program halts execution. The reason for this is so that when the user views a chart, which requires temporarily exiting the userform to view a chart sheet, I need to make sure that they cannot make modifications to the chart, access any other part of the program’s workbook and inadvertently alter things that the program needs to operate correctly. What I have noticed is that from time-to-time all of the menu items will not be deactivated or toolbars that were active before the program began execution are not reactivated when execution of the program ceases. In the previous case, only the custom menu I created to return to the userform should be active and in the latter case, only a forced quit should result in the toolbars not being reset to their previous visibility status.

It seems as if sometimes, VBA just ignores code. That should not be the case, but that is what appears to be occurring. I have even stepped through code with the debugger to verify that the code is being executed, it is, but then I being on the back end of Excel makes it difficult to see what is going on with the application as switching to the app while a program is running will usually display the active userform over the VBA Editor instead of switching to the worksheets view. Oftentimes, you will have to do a bit of trial and error to get VBA to do just what you want.