Menu
(Final edit: The code I ended up putting together that works is way down below, it's probably the final reply in this thread. :-) )
I'm trying to write generic copy-and-paste code that will work in a standalone VBScript (a .vbs file), in a .hta file, and as VBA (for instance, in an Excel file). To do that, I need some way for the code itself to tell what engine it's running in.
The best idea I've heard so far involved testing if certain objects exist or not, but in VBA, that fails at compile time (so I can't bypass it with On Error), so that didn't work out. Trying to find out the name of the file it's running didn't end up being viable; that's one of the things that's done differently depending on which of the three script engines the code's running in. I would love to have something simple like this, but am not sure what to fill it in with:
Edit: Most responses so far involve checking for possibly non-existent objects, which outright does not work in VBA with Option Explicit on (it throws a compile-time error, so On Error doesn't work, and turning off Option Explicit is not an option). Is there some other roundabout / out-of-the-box way to find out what's needed here?
Option Explicit
'--- Returns a string containing which script engine this is running in,
'--- either "VBScript", "VBA", or "HTA".
Function ScriptEngine()
If {what goes here?} Then ScriptEngine="VBS"
If {what goes here?} Then ScriptEngine="VBA"
If {what goes here?} Then ScriptEngine="HTA"
End Function
If this is filled in right, you should be able to copy and paste that function into any VBA, VBS, or HTA file without modification, call it, and get a result instead of an error, even when Option Explicit is on. What's the best way to go about this?
804
The restriction on requiring Option Explicit in the VBA implementation makes this a little more difficult than it otherwise would be (it's a one-liner without it)... Ironically it also turns out to be the key to the solution.
If you don't limit yourself to a single function, you can get away with it by doing something like this:
Dim hta
Sub window_onload()
hta = True
End Sub
Function HostType()
On Error Resume Next
If hta Then
HostType = "HTA"
Else
Dim foo
Set foo = foo
If Err.Number = 13 Then
HostType = "VBA"
Else
HostType = "VBS"
End If
End If
End Function
It works like this - If it's loaded via an HTA file, the window_onload event handler runs, setting the hta variable to True. That's the first test. The second "test" is on the error thrown by the line Set foo = foo. This is a type mismatch in VBA, where it is interpreted as trying to Set a Variant to Empty, which isn't a compatible type. The same line of code throws an error 424 (Object required) in VBScript because it is not a strongly typed language. That means VBA's type check is skipped and it attempts to actually perform the assignment (which fails). The rest is just figuring out how it threw and returning the result.
VBA
Option Explicit
Dim hta
Sub Test()
Debug.Print HostType 'VBA
End Sub
Sub window_onload()
hta = True
End Sub
Function HostType()
On Error Resume Next
If hta Then
HostType = "HTA"
Else
Dim foo
Set foo = foo
If Err.Number = 13 Then
HostType = "VBA"
Else
HostType = "VBS"
End If
End If
End Function
VBScript
WSCript.Echo HostType
Dim hta
Sub window_onload()
hta = True
End Sub
Function HostType()
On Error Resume Next
If hta Then
HostType = "HTA"
Else
Dim foo
Set foo = foo
If Err.Number = 13 Then
HostType = "VBA"
Else
HostType = "VBS"
End If
End If
End Function
HTA
<HTML>
<BODY>
<script type="text/vbscript">
Dim hta
Sub Test()
MsgBox HostType
End Sub
Sub window_onload()
hta = True
End Sub
Function HostType()
On Error Resume Next
If hta Then
HostType = "HTA"
Else
Dim foo
Set foo = foo
If Err.Number = 13 Then
HostType = "VBA"
Else
HostType = "VBS"
End If
End If
End Function
</script>
<button onclick="vbscript:Test()">Click me</button>
</BODY>
</HTML>
EDIT:
FWIW, the one-liner referenced above if Option Explicit isn't needed is simply this:
Function HostString()
HostString = Application & document & WScript
End Function
All three objects have a default property that returns a String. In VBScript, this will return "Windows Script Host". In VBA, it will return the name of the host (i.e. "Microsoft Excel" in Excel). In HTA it will return "[object]".
69
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
