Category Archives: VBScript

VBScript – Convert Image To Base 64

Last week I wrote a function which takes a file path and returns the base64 PNG code for that image. Since it isn’t straight-forward for everyone as to how to convert that JScript code to VBScript I decided to do just that:

Public Function convertImageToBase64(filePath)
  Dim inputStream
  Set inputStream = CreateObject("ADODB.Stream")
  inputStream.Open
  inputStream.Type = 1  ' adTypeBinary
  inputStream.LoadFromFile filePath
  Dim bytes: bytes = inputStream.Read
  Dim dom: Set dom = CreateObject("Microsoft.XMLDOM")
  Dim elem: Set elem = dom.createElement("tmp")
  elem.dataType = "bin.base64"
  elem.nodeTypedValue = bytes
  convertImageToBase64 = "data:image/png;base64," & Replace(elem.text, vbLf, "")
End Function

Feel free to use this code in your own projects. 8-)

VBA – Encode URL Query Parts In Excel

Have you ever had a column text for which you wish you could easily create links? For example, let’s say that you have a column of search words (or phrases) that should link to the Google page for each search. How would you do it? First of all, you would probably want to be able to build the URL in a separate column. Unfortunately, Excel doesn’t provide a way of encode URL parameters. Therefore, you can use the following as your own UDF:

Public Function encodeURL(ByVal queryPart As String)
    Dim c As String
    While Len(queryPart) > 0
        c = Left(queryPart, 1)
        queryPart = Mid(queryPart, 2, Len(queryPart) - 1)
        If c Like "[A-Za-z0-9._~-]" Then
            encodeURL = encodeURL & c
        ElseIf c = " " Then
            encodeURL = encodeURL & "+"
        Else
            encodeURL = encodeURL & "%" & Right("0" & Hex(Asc(c)), 2)
        End If
    Wend
End Function

That function will take in one string and output the equivalent as an encoded URL query part. That function should be the equivalent of PHP’s urlencode() function. Tomorrow, we will see how this function can actually help you to be able to create links for an entire column.

Regular Expression Examples

One of the things that I love about string manipulation is the existence of regular expressions. For this reason, I have decided to share a few examples that may help those who are learning about regular expressions so that can understand them a bit better.


JavaScript – General Variable Name

In many languages, a variable must start off with a letter and may be followed by letters, numbers, and/or the underscore character.  Knowing this, we could use the following regular expression in JavaScript to match a variable name:

var expVarIFlag = /^[A-Z]\w*$/i;

Basically, the above regular expression will only match a string if it matches the pattern for a variable name. The reason that I only put [A-Z] and not [A-Za-z] is because in JavaScript you can specify an “i” flag after the regular expression which indicates that the expression will be case-insensitive. Another thing to note is that I used the \w class which basically represents a word. A word in regular expressions typically means any letter (A to Z regardless of case), any digit (0 to 9), or the underscore character. The reason I used the asterisk instead of the plus sign is because a variable may be just one letter.

NOTE: Although this regular expression may work for other languages, in JavaScript, a variable name can also start off with an underscore or dollar sign.


PostgreSQL – Date (MM/DD/YYYY)

Even though using a regular expression shouldn’t be the way to completely validate a date, you can do so partially with the following in PostgreSQL:

SELECT id, text
FROM answers
WHERE text ~ '^(0\\d|1[012])/([012]\\d|3[01])/\\d{4}$';

The above query will pull all of the answers with a text that matches the pattern to see if it looks like a valid date.

  1. First it specifies that the first two characters are a 0 followed by another digit or a 1 followed by either a 0, 1, or 2.
  2. Next should come a forward slash.
  3. Next should be either…
    1.  0, 1, or 2 followed by any digit
    2. or 3 followed by a 0 or 1
  4. Finally should be another forward slash followed by four digits.

One thing to notice is that in order to properly escape the class inside of a string (which is what we have to do here in PostgreSQL), you have to escape the backslash so that it will be interpreted as one backslash in front of the next character thus rendering “\\w” as “\w“.


PHP – Hexadecimal Color Code

In CSS, a color code can be in many different forms. One accepted form is hexadecimal. The hex form can be three characters or six characters long. It can start off with a number sign, but this symbol isn’t required. Knowing all of this, we could use the following in PHP to validate the hex color:

$pattern = '/^#?([0-9A-F]{3}){1,2}$/i';
$validHex = preg_match($pattern, $_GET['hex']);

The preg_match() function is used to validate the GET parameter called “hex” against our regular expression:

  1. First it specifies that the first character may be a number sign (#).
  2. Next I have defined a parenthesized group which matches any three hexadecimal digits.
  3. After that, I am specifying that my parenthesized group pattern may appear once or two times in a row and that no other characters should follow.
  4. Finally, you will notice that I am again using the “i” flag to indicate that this is a case-insensitive pattern.

Python – Simple Image File Names

Let’s use Python now to check to see if a file name looks like a valid image name:

# Import the regular expression library
import re

# Defining the compiled regular expression.
pat = "^[^/\\?%*:|\"<>]+\\.(jpg|png|gif|bmp)$"
reImg = re.compile(pat, re.I)

# Getting the file name from the user
fileName = raw_input("File name:  ")

# Determine if the file name is an image name
isImage = reImg.match(fileName) is not None

The regular expression created does the following:

  1. First makes sure that the string starts off with one more characters which are none of the following:  /  \  ?  %  *  :  |  ”  <  >
  2. In the end it checks that a dot is found followed by one of the following extensions which must appear at the end of the string:
    1. jpg
    2. png
    3. gif
    4. bmp
  3. It is also important to note that by using “re.I“, I specified that casing would be ignored.

The code should basically prompt the user for a file name and then validate the string entered to determine if it matches the regular expression for an image.  The boolean value indicating whether or not it is an image is stored in the isImage variable.


VBScript – Format Large Integer With Commas

The following is how you could use a regular expression to insert commas into a number (integer):

' Setup the RegExp for testing if input is an integer.
Dim re : Set re = new RegExp
re.Pattern = "^(0|-?[1-9]\d*)$"

' Get the input integer from the user.
input = InputBox("Enter an integer", "Your Integer", 123456789)

' If the input is an integer...
If re.Test(input) Then
  ' Modify the pattern to input the commas correctly.
  re.Pattern = "(\d)(?=(\d{3})+$)"
  re.Global = True

  ' Reformat the integer, if given.
  newInput = re.Replace(input, "$1,")

  ' Display the input formatted with commas.
  MsgBox input & " became " & newInput

' If the input is not an integer, tell the user so.
Else
  MsgBox "The input given wasn't recognized as an integer."
End If

The first regular expression basically tests to make sure that the input is either simply a zero or one or more digits with the first one being non-zero. In other words, the first pattern makes sure that the input is an integer that doesn’t start with a zero (unless it is zero). The second regular expression is what is used to insert the comma(s) in the right place(s). It finds every instance in which one digit is followed by at least one group of three digits. By starting the group off with “?=” I am ensuring that the matched group will not be skipped on the next pass through.

JScript – Using InputBox & MsgBox

Over the years, I have seen many different techniques for emulating the InputBox and MsgBox functions in JScript, but none of them were quite as elegant as the one I saw here. Once I dug into the code and realized that ScriptControl was making it so that JScript could run VBScript commands, I felt a whole new world of possibilities had been found. Therefore, my first test was to make a better functioning InputBox and MsgBox function available to JScript by using the following code:

(function(vbe) {
  vbe.Language = "VBScript";
  vbe.AllowUI = true;

  var constants = "OK,Cancel,Abort,Retry,Ignore,Yes,No,OKOnly,OKCancel,AbortRetryIgnore,YesNoCancel,YesNo,RetryCancel,Critical,Question,Exclamation,Information,DefaultButton1,DefaultButton2,DefaultButton3".split(",");
  for(var i = 0; constants[i]; i++) {
    this["vb" + constants[i]] = vbe.eval("vb" + constants[i]);
  }

  InputBox = function(prompt, title, msg, xpos, ypos) {
    return vbe.eval('InputBox(' + [
        toVBStringParam(prompt),
        toVBStringParam(title),
        toVBStringParam(msg),
        xpos != null ? xpos : "Empty",
        ypos != null ? ypos : "Empty"
      ].join(",") + ')');
  };

  MsgBox = function(prompt, buttons, title) {
    return vbe.eval('MsgBox(' + [
        toVBStringParam(prompt),
        buttons != null ? buttons : "Empty",
        toVBStringParam(title)
      ].join(",") + ')');
  };

  function toVBStringParam(str) {
    return str != null ? 'Unescape("' + escape(str + "") + '")' : "Empty";
  }
})(new ActiveXObject("ScriptControl"));

What makes this code better is the fact that it will accept any string (even those with special characters). It is also better because it allows for null parameters to be passed to the VBScript functions. Finally, this script makes all of the MsgBox constants such as vbRetryCancel and vbInformation available. The following are example calls made to the defined functions:

var name = InputBox('I am "Script-101".\nWhat is your name?', "Name");
var greetings = name
  ? 'Nice to meet you "' + name + '".'
  : "That's fine, you don't have to tell me who you are.";
MsgBox(greetings, name ? vbInformation : vbCritical, "Greetings");

You can download the JScript file that contains these two code blocks and then try to run it on your Windows PC.  If you are running a 32bit system, this script will work as expected. On the other hand, if you are running a 64bit version of Windows, this script will error out with the message “Automation server can’t create object”.  The reason for this is that “ScriptControl”, which is being used to allow JScript to call VBScript functions, isn’t available in the 64bit version of cscript or WScript.  Therefore, to run this script, you will have to do so using the 32bit version.  You can do this by doing the following:

  1. Go to the start menu
  2. If you are not using Windows Seven, click “Run”
  3. type %windir%\SysWoW64\cmd.exe
  4. Click OK
  5. Now type cscript /path/to/the/script.vbs (change the path to whatever the path to the script is)

JavaScript Snippet – Array.prototype.toVBArray()

I was writing two HTAs (HTML Applications) today and for the first one, I needed to create a VBArray with the number five in it. Fortunately, I remembered that I wrote a page about this prior to my blog. Therefore, here is the snippet which I plan on including in the next version of jPaq:

// Returns the array as a VBArray.
Array.prototype.toVBArray = function() {
  var dict = new ActiveXObject("Scripting.Dictionary");
  for(var i = 0, len = this.length; i < len; i++)
    dict.add(i, this[i]);
  return dict.Items();
};

Here is an example of how to use it:

var avbArray = ([1,4,5]).toVBArray();

Of course, this code is only useful in Internet Explorer (HTML or HTA) or in a standalone JScript file. For all of you who have to deal with VBArrays in JavaScript, have fun!!! 8)

VBScript – RegExp Replace Using A Callback Function

One of the nice things about JavaScript is its functional nature. This is especially nice when it comes to dealing with strings and regular expressions. For instance, in JavaScript, you can use the following code to capitalize every other letter in a string:

var str = "where in the world is carmen sandiego?";
var strWeird = str.replace(/(.)(.)/g, function(a,b,c) {
  return b.toUpperCase() + c;
});
alert(strWeird); // WhErE In tHe wOrLd iS CaRmEn sAnDiEgO?

Cool stuff, right? Wouldn’t it be nice to be able to use a similar approach in VBScript? Believe it or not, you can? Here is the definition for a function which will allow you to do something similar:

Function RegExpReplace(re, str, replacement)
	' If replacement is a string, use the native RegExp.Replace function.
	If TypeName(replacement) = "String" Then
		RegExpReplace = re.Replace(str, replacement)
	' Since replacement is not a string, call replacement with every match
	' object and replace the match with the return value.
	Else
		Dim mc, m, ret, offset
		offset = 0
		Set mc = re.Execute(str)
		For Each m In mc
			ret = replacement(m)
			str = Left(str, m.FirstIndex - offset) & ret _
				& Mid(str, m.FirstIndex + m.Length - offset + 1)
			offset = offset + m.Length - Len(ret)
		Next
		RegExpReplace = str
	End If
End Function

The above function takes three parameters: the regular expression, the string that may be changed and the replacement function (or string). Now the question is, how do we pass the function (or at least a reference to it)? We can do this by taking the name of the function and using the GetRef function to get a reference to it. The following is the equivalent of what was done in JavaScript at the onset of this post:

Function fnUp1(objMatch)
  fnUp1 = UCase(m.Submatches(0)) & m.Submatches(1)
End Function

Dim re: Set re = New RegExp
re.Pattern = "(.)(.)"
re.Global = True

Dim str: str = "where in the world is carmen sandiego?";
Dim strWeird: strWeird = RegExpReplace(re, str, GetRef("fnUp1"))
MsgBox strWeird ' WhErE In tHe wOrLd iS CaRmEn sAnDiEgO?

Okay, of course the code is not as short in JavaScript, because of the way that regular expressions must be created and the fact that anonymous functions don’t exist in the language, but this is just a simple example. You may need to use this function in many different places in your code.

The other thing that I briefly mentioned is that the third parameter may be a string instead of a reference to a function. This is basically a shortcut for the RegExp.Replace function which natively exists.

Now you see that it is possible to script in a functional way with VBScript. Still, as is evidenced by the examples, JavaScript (and JScript) can usually accomplish the same thing with less code. :D

VBScript – CamelCase Function

One of the things that is often used in the JavaScript Prototype library is the camelize function which camel-cases the specified string. Unfortunately, just as this function doesn’t exist natively in JavaScript, it also doesn’t in VBScript. If you do need an equivalent function to that which is available in the Prototype library, you can use the following function definition:

Public Function CamelCase(str)
  Dim arr, i
  arr = Split(str, "-")
  For i = LBound(arr) + 1 To UBound(arr)
    arr(i) = UCase(Left(arr(i), 1)) & Mid(arr(i), 2)
  Next
  CamelCase = Join(arr, "")
End Function

To use this function, simply pass the string that you want to be camel-cased, and the modified string will be returned:

MsgBox CamelCase("border-color-left")

VBScript To JavaScript – Space Function

In VBScript, there is a space function which takes an integer and returns a string with the specified number of spaces. At times, you may need such a function to make you text look neat while working with a monospace font. Here is the code that you could use to define your own space function in JavaScript:

function space(num) {
  return new Array(num + 1).join(" ");
}

Basically, the space function creates an array of a length that is one more than the number that is specified. After that, the array of undefined values is joined together with the delimiter of a space (thus the reason for using num + 1 to ensure the correct amount of spaces). Finally, this string of spaces is returned.