Category Archives: VBA

Visual Basic for Applications

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
  Wend
  ie.Document.parentWindow.execScript "n = (new Date).getTimezoneOffset()"
  GetTimezoneOffset = ie.Document.parentWindow.n
  ie.Quit
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)
  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")