From Zero to VBA Hero
Today I wanted to showcase a simple HTA example. There are tons of VBA developers out there currently. These are often persons who started their developer journeys as analysts or simply Excel powerusers. At some point in time Excel native features are not sufficient to tackle some complex tasks and drive users to learn VBA. It is quite an easy step often as, it is important to remember that Excel has 2 important advantages over other programming languages:
- It is available almost on any Windows workstation (as most have MS Office installed)
- It features a built in development environment.
Without having to install any additional software, any analyst or other corporate person can use VBA to achieve automation of certain tasks. In a series of posts I will want to encourage some VBA devs to explore some alternatives and new programming possibilities.
What is HTA?
HTA is the abbreviation for Html Application. See the Wiki page for more details. In short, however, it is simply a HTML file with a “.hta” file extension that can runs on Internet Explorer, embedded with scripts. Well now, some might say, is it simply a HTML file then? Well, hta file have elevated security privileges allowing them to run like regular desktop apps!
HTA can run like desktop applications being embedded in Internet Explorer instead of e.g. Excel or Word in case of VBA. Additionally as these are HTML files you can build a rich user interface and a much more pleasant user experience. But let’s jump right in!
Why explore HTA?
From my experience many VBA developers refrain from learning other programming languages, noticing that basically they build roughly any solution or automation resorting only to VBA. This is a common trap due to which many MS Office-based monster-complex solutions are developed which should not have seen the light of day. I have seen many monsters… from complex BI models (calculations running for hours) to … complex databases shared by multiple users at the same time (oh the horror!). Although all these solutions were quite amazing they pushed the limits of VBA more than they should. I won’t ponder to much on the reasons for these solutions to come to existance. Rather I would like to show some alternatives.
In today’s post I wanted to encourage VBA dev’s to make their first step out of their VBA caves and to leave Excel or any other MS Office environment they use on their daily basis. I know this comes as a shock for some so I promise we will stick with Visual Basic for now.
So now let us say we want to build a regular desktop app not one hosted with an Excel or Word application. Assuming we do not want to move to more complex solutions like .NET or Java, VBA devs are encouraged to explore HTA.
HTA Example: Hello World!
Let’s start by creating an hta file. Create a text file and change the extension to .hta
Next let’s insert some Hellow World code in there:
<html> <head> <HTA:APPLICATION ID="HelloExample" BORDER="thin" BORDERSTYLE="complex" maximizeButton="yes" minimizeButton="yes" /> <script type="text/vbscript"> Sub Hello() MsgBox "Hello" End Sub </script> <title>AnalystCave: HTA Hello World!</title> </head> <body> <table width="100%"> <tr> <td> <!--Your content here!--> <H1>Hello World!</H1> <input type="Button" onClick="Hello()" value="Hello" /> </td> </tr> </table> </body> </html>
Now double click and that’s it!
Simple huh? Now to analyze the code…
If you are familiar with HTML you will notice that the HTA:APPLICATION clearing does not belong in a regular HTML file. This tag defines the HTA application and includes params that let you specify how the application window will look like.
Here you will find more on the HTA application params: HTA Application Params
Notice also the script tag:
<script type="text/vbscript"> Sub Hello() MsgBox "Hello" End Sub </script>
This procedure is linked to the HTML button so when clicked you will see a simple Hello message.
Accessing Excel files and resources in HTA
I added a simple Excel file to the directory:
This Excel file contains a single data cell:
Now let us add a piece of code to the HTA file to access the data cell. Let’s replace the previous script tag with this:
<script type="text/vbscript"> Sub Hello() Set objShell = CreateObject("WScript.Shell") myCur = objShell.CurrentDirectory dim excelApp, excelWb, excelVal set excelApp = CreateObject("Excel.Application") set excelWb = excelApp.Workbooks.Open(myCur & "Test.xlsx") excelVal = excelWb.Worksheets(1).Range("A1").Value excelWb.Close excelApp.Quit MsgBox excelVal End Sub Sub ExitForm() Close End Sub </script>
Now when we open the HTA and hit the Hello button we will see the contents of the data cell.
All this is just a drop in the ocean of the possibilities of using HTA.
Expect more on this topic soon :). Let me know what you think!