Google Apps Script equivalents for common VBA functions

May 27, 2012 From VBA to Google Apps Script 2

As part of creating a framework for dual maintenance of Google Apps Script and VBA versions of the same application, one of the key things was to produce a set of functions in GAS that would be equivalent to the most common ones in VBA. This includes various objects missing from GAS such as the VBA collection.

Here is a selection of the GAS equivalent of common VBA functions. For more like this, take a look at the ramblings site, or contact me on our forum.

Page Content hide
1 Strings
2 User Interaction
3 Conversion
4 Informational and general

Strings

function LCase(s) {
  return s.toLowerCase();
}
function Trim(s) {
  return LTrim(RTrim(s));
};
function Len(s) {
  return s.length;
}
function Left(str,optLen) {
  return Mid( str, 1 , optLen);
}
function Right(str,optLen) {
  var length = IsMissing (optLen) ? Len(str) : optLen;
  var start = 1+ Len(str) - length;
  return Mid( str, start );
}
function Mid (str,optStart,optLen) {
  var start = IsMissing (optStart) ? 0 : optStart - 1;
  var length = IsMissing (optLen) ?  Len(str) - start + 1 : optLen ;
  DebugAssert( str.slice, str + ' is not a valid string for Mid function');
  return  str.slice ( start, start + length);
  
}
function Split(s,optDelim,optLimit) {
  return s.split(fixOptional(optDelim,","),fixOptional(optLimit,-1));
};

function LTrim(s) {
  return CStr(s).replace(/^\s\s*/, "");
};
function Rept(n,s){
  return n ?  Array(n).join(s) : '';
}
function Space(n){
  return Rept(n," ");
}
function RTrim(s) {
  return CStr(s).replace(/\s\s*$/, "");
};

User Interaction

function MsgBox(a) {
    Browser.msgBox(a);
}
function InputBox(a) {
    return Browser.inputBox(a);
}

Conversion

function CStr(v) {
  return v ? v.toString() : '';
}
function DateSerial(y,m,d){
  return new Date(y,m,d);
}
function Year(dt){
  return dt.getFullYear();
}

Informational and general

function Empty() {
  return "";
}
function vbLf() {
  return "\n";
}
function IsEmpty(cellData) {
  return typeof(cellData) == "string" && cellData == Empty();
}
function IsDate(sDate) {
  var tryDate = new Date(sDate);
  return (tryDate.toString() != "NaN" && tryDate != "Invalid Date") ;
}
function IsNumeric(n) {
  return !isNaN(parseFloat(n)) && isFinite(n);
}
function IsMissing (x) {
  return isUndefined(x);
}
function IsObject (x) {
  return VarType(x) == 'object';
}
function VarType (v) {
  return typeof v;
}

function Xor (a,b) {
  return a ? !b : b ;
}
function Abs (x) {
  return Math.abs(x);
}

 

About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.
Twitter

2 Comments

  1. This is a great resource for people converting from Excel to Google spreadsheets. It would be great if you could publish this set of functions to the script gallery, to make it easy for people to use them in their spreadsheets.

Comments are closed.