Category Archives: VBA

Visual Basic for Applications

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.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 & "+"
            encodeURL = encodeURL & "%" & Right("0" & Hex(Asc(c)), 2)
        End If
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.

POW Answer – Explain That SQL #1

To answer last week’s POW, the purpose of the SQL was to generate a random string of letters and numbers. I actually wrote two different JavaScript function that can produce the same results. The following is the slightly more straight-forward solution:

function randomChars(len) {
  for(var n, s = ""; --len;) {
    n = parseInt(Math.random() * 62);
    s = String.fromCharCode(n + (n < 26 ? 65 : n < 52 ? 71 : -4));
  return s;

The next version is a bit harder to follow because it involves recursion:

function recRandomChars(len) {
  if(!len){ return ""; }
  var n = parseInt(Math.random() * 62);
  return recRandomChars(--len)
    + String.fromCharCode(n + (n < 26 ? 65 : n < 52 ? 71 : -4));

One practical application of this is to generate a password with random characters.

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)

VBA Snippet – GetTimezoneOffset()

One of the things I think is amazing about VBA is the fact that it is so incomplete in some ways. For instance, did you know that there is no native way to get the local timezone offset. Yesterday I was working on a VBA SOAP plugin for Excel and realized this horrible fact. Of course, my first instinct was to Google the answer. Unfortunately, not even Google could provide a satisfactory (meaning short) solution. Therefore, I came up with the following VBA function:

Public Function GetTimezoneOffset() As Integer
  Dim ie As New InternetExplorer
  ie.Visible = False
  ie.Navigate "about:blank"
  While ie.readyState <> READYSTATE_COMPLETE And Not ie.Busy
  ie.Document.parentWindow.execScript "n = (new Date).getTimezoneOffset()"
  GetTimezoneOffset = ie.Document.parentWindow.n
End Function

Of course, there are downsides to this solution. The first (being minor) is the fact that you have to include a reference by going to Tools > References… and then selecting “Microsoft Internet Controls”. The second is the fact that every time this function is called, an instance of Internet Explorer is created and then quickly (speed depending on the computer) destroyed. Other than that, the function will work. Its purpose is to return the amount of minutes behind GMT (or UTC) time the computer is. This is the equivalent of doing the following in JavaScript:

var offset = (new Date).getTimezoneOffset();

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)
  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")