hta example

Like VBA? You will love HTA! (HTA example using VBS)

Written by on in VBscript

Comfortable with VBA? Your clients/users need VBA solutions but want them to be lightweight GUI, not cumbersome Excel Workbooks with Macro restrictions? Well why not try HTML Application (HTA)? What’s HTA? It’s a simple HTML application embedded with Visual Basic script or Javascript, or even both if needed! Don’t see the opportunities yet? Imagine a cool Web GUI (feel free to leverage Jquery or other libraries) embedded with a cool feature you already have programmed in Visual Basic. No need to install or open Excel! What is more HTA applications are simply text files with a HTA extension, hence, anyone can easily open and edit them in a simple text editor. Neat right? Interested – read on.

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:

  1. It is available almost on any Windows workstation (as most have MS Office installed)
  2. 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
HTA file icon
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!

HTA Example: Hello World!
HTA Example: Hello World!

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

See also  The VBA Array tutorial

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

As HTA files have elevated rights I like to think of them as Vbscript files extended with a user interface. Bascially all Vbscript and Javascript commands are valid in HTA. Let us see a simple example that exercises some of these rights – like accessing Excel files.

I added a simple Excel file to the directory:

Excel and HTA
Excel and HTA

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.

Modified HTA accessing Excel file
Modified HTA accessing Excel file

All this is just a drop in the ocean of the possibilities of using HTA.

Summary

HTA extends new possibilities to VBA developers and provides them we a simple means of making desktop applications in Windows without embedding them in Excel nor other MS Office applications. In my opinion HTA encourages VBA developers to also explore new programming languages and scripts as with HTA you need to learn HTML and probably some Javascript. Over time you will probably learn CSS and start using some cool Javascript libaries like jQuery, AngularJS. HTA enables you to create rich desktop applications while also giving you the safe-haven of being able to keep using VBscript (almost identical to VBA).

Expect more on this topic soon :). Let me know what you think!

Comments are closed.