Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Apps Script equivalents for common VBA function
/** @description
* javaScript/Google Apps script functions that are equivalent to common VBA functions
* in general these provide the same functionality and have the same calling stack
* See http://ramblings.mcpher.com/Home/excelquirks/codeuse for more details
* @author <a href="mailto:bruce@mcpher.com">Bruce McPherson</a><a href="http://ramblings.mcpher.com"> ramblings.mcpher.com</a>
*/
/**
* Removes leading and trailing whitespace
* @param {string|number} v the item to be trimmed
* @return {string} The trimmed result
*/
function Trim(v) {
return LTrim(RTrim(v));
};
/**
* Removes leading whitespace
* @param {string|number} s the item to be trimmed
* @return {string} The trimmed result
*/
function LTrim(s) {
return CStr(s).replace(/^\s\s*/, "");
};
/**
* Removes trailing whitespace
* @param {string|number} s the item to be trimmed
* @return {string} The trimmed result
*/
function RTrim(s) {
return CStr(s).replace(/\s\s*$/, "");
};
/**
* gets the .toString length
* @param {string|number} v the item
* @return {number} The length
*/
function Len(v) {
return CStr(v).length ;
}
/**
* gets the leftmost portion of an item
* @param {string|number} str the item
* @param {number=} optLen length of result(default all)
* @return {string} The left portion of the string
*/
function Left(str,optLen) {
return Mid( str, 1 , optLen);
}
/**
* gets the rightmost portion of an item
* @param {string|number} str the item
* @param {number=} optLen length of result(default all)
* @return {string} The right portion of the string
*/
function Right(str,optLen) {
return Mid( str, 1 + Len(str) - fixOptional ( optLen, Len(str) ) );
}
/**
* gets and extract from a string
* @param {string|number} str the item
* @param {number=} optStart start position(base 1) of extract
* @param {number=} optLen Number of characters (default all remaining)
* @return {string} The extracted string
*/
function Mid (str,optStart,optLen) {
var s = CStr(str);
var start = IsMissing (optStart) ? 0 : optStart - 1;
start = start < 0 ? 0 : start;
var length = IsMissing (optLen) ? Len(s) - start + 1 : optLen ;
DebugAssert( s.slice, s + ' is not a valid string for Mid');
return s.slice ( start, start + length);
}
/**
* Split
* Splits an item into an array of strings
* @param {string|number} s the item
* @param {string=} optDelim delimiter(default ,)
* @param {number=} optLimit max number of splits(default all)
* @return {Array.<string>} The split arrray of strings
*/
function Split(s,optDelim,optLimit) {
return CStr(s).split(fixOptional(optDelim,","),fixOptional(optLimit,-1));
};
/**
* Rept
* Returns a string of the same character repeated n times
* @param {number} n number of times to repeat
* @param {string=} s the character to repeat (default ' ');
* @return {string} the string of repeats
*/
function Rept(n,s){
return n > 0 ? Array(n+1).join(CStr(fixOptional(s,' '))) : '';
}
/**
* Space
* Returns a string of ' ' repeated n times
* @param {number} n number of times to repeat
* @return {string} the string of blanks
*/
function Space(n){
return Rept(n);
}
/**
* LCase
* Returns a string converted to lower case
* @param {string} s item to be converted
* @return {string} item in lower case
*/
function LCase(s) {
return CStr(s).toLowerCase();
}
/**
* UCase
* Returns a string converted to upper case
* @param {string} s item to be converted
* @return {string} item in upper case
*/
function UCase(s) {
return CStr(s).toUpperCase();
}
/**
* Chr
* Returns a string representing a numeric char code
* @param {number} n numeric code
* @return {string} the equivalent character
*/
function Chr(n) {
return String.fromCharCode(n);
}
/**
* Asc
* Returns a numeric char code given a character
* @param {string} s the character
* @return {number} the equivalent code
*/
function Asc(s) {
return s.charCodeAt(0);
}
/**
* InStr
* Returns the position at which a string starts(base1)
* @param {number=} optStart the position to start looking from(default 1)
* @param {string} inThisString the the string to lookin
* @param {string} lookFor the string to look for
* @param {number=} optCompare not yet implemented
* @return {number} the position the string starts at or 0 if not found
*/
function InStr(optStart,inThisString,lookFor,optCompare) {
// TODO optCompare
var start = fixOptional (optStart, 1);
var s = Mid (inThisString, start);
var p = s.indexOf(lookFor);
return (s && lookFor) ? (p == -1 ? 0 : p+start ): 0;
}
/**
* InStrRev
* Returns the position at which a string starts(base1), starting at the end
* @param {string} inThisString the the string to lookin
* @param {string} lookFor the the string to look for
* @param {number=} optStart the position to start looking from(default: the end)
* @param {number=} optCompare not yet implemented
* @return {number} the position the string starts at or 0 if not found
*/
function InStrRev(inThisString,lookFor,optStart,optCompare) {
// TODO optCompare
var start = fixOptional (optStart, -1);
var s = CStr(inThisString);
start = start == -1 ? Len(s) : start ;
return (s && lookFor) ? s.lastIndexOf(lookFor,start-1)+1 : 0;
}
// Date functions
/**
* DateSerial
* Returns a date object
* @param {number} y year
* @param {number} m month
* @param {number} d day
* @return {Date} a date object
*/
function DateSerial(y,m,d){
return new Date(y,m,d);
}
/**
* Year
* Returns the year from a date
* @param {Date} dt a date object
* @return {number} the year
*/
function Year(dt){
return dt.getFullYear();
}
// Conversion functions
/**
* Cstr
* Returns item converted to a string
* @param {*} v item to be converted
* @return {string} item converted to a string
*/
function CStr(v) {
return v===null || IsMissing(v) ? ' ' : v.toString() ;
}
// Maths functions
/**
* Xor
* Returns item converted to a string
* @param {boolean} a first item
* @param {boolean} b second item
* @return {boolean} exclusive OR of two items
*/
function Xor (a,b) {
return a ? !b : b ;
}
/**
* Abs
* Returns absolute value of a number
* @param {number} x value
* @return {number} absolute value
*/
function Abs (x) {
return Math.abs(x);
}
// Informational functions
/**
* IsEmpty
* Returns whether this is an 'empty' value
* @param {*} v item to check
* @return {boolean} true if item is empty
*/
function IsEmpty(v) {
return typeof(v) == "string" && v == Empty();
}
/**
* IsDate
* Returns whether item is a valid date
* @param {string} sDate item to check
* @return {boolean} true if item can be converted to a date
*/
function IsDate(sDate) {
var tryDate = new Date(sDate);
return (tryDate.toString() != "NaN" && tryDate != "Invalid Date") ;
}
/**
* IsNumeric
* Returns whether item is a valid number
* @param {string} s item to check
* @return {boolean} true if item can be converted to a number
*/
function IsNumeric(s) {
return !isNaN(parseFloat(s)) && isFinite(s);
}
/**
* IsMissing
* Returns whether item is a missing argument
* @param {*} x item to check
* @return {boolean} true if item is undefined
*/
function IsMissing (x) {
return isUndefined(x);
}
/**
* IsObject
* Returns whether item is an object
* @param {*} x item to check
* @return {boolean} true if item is an object
*/
function IsObject (x) {
return VarType(x) == 'object';
}
/**
* IsArray
* Returns whether item is an array
* @param {*} x item to check
* @return {boolean} true if item is an array
*/
function IsArray (x) {
return isArray(x) ;
}
/**
* IsNull
* Returns whether item is null
* @param {*} x item to check
* @return {boolean} true if item is exactly null
*/
function IsNull (x) {
return x===null ;
}
/**
* VarType
* Returns whether item is null
* @param {*} v item to check
* @return {string} the java script type
*/
function VarType (v) {
return typeof v;
}
//Constant replacements
/**
* Empty
* Returns empty
* @return {string} that satisfies IsEmpty()
*/
function Empty() {
return "";
}
/**
* vbLf
* Returns LF
* @return {string} line feed character
*/
function vbLf() {
return "\n";
}
// interaction functions
/**
* MsgBox
* Displays a dialog box
* @param {string} a message to display
*/
function MsgBox(a) {
// cant do this as a library
try {
Browser.msgBox( a);
}
catch (err) {
DebugPrint('MsgBoxSubstitute',a);
}
}
/**
* InputBox
* Displays a dialog box and gets input
* @param {string} a message to display
* @return {string} user input
*/
function InputBox(a) {
return Browser.inputBox(a);
}
// Sheet access functions
/**
* Sheets
* Gets a sheet
* @param {string} wn sheet Name
* @return {Sheet} a sheet
*/
function Sheets(wn) {
return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(wn);
}
/**
* WorkSheets
* Gets a sheet
* @param {string} wn sheet Name
* @return {Sheet} a sheet
*/
function WorkSheets(wn) {
return Sheets(wn);
}
/**
* ActiveWorkbook
* Gets the active workbook
* @return {Spreadsheet} a workbook
*/
function ActiveWorkbook() {
return SpreadsheetApp.getActiveSpreadsheet();
}
/**
* ActiveSheet
* Gets the active sheet
* @return {Sheet} a sheet
*/
function ActiveSheet() {
try {
return SpreadsheetApp.getActiveSheet();
}
catch(err) {
return null;
}
}
/**
* ActiveRange
* Gets the active range
* @return {Range} a range
*/
function ActiveRange() {
return SpreadsheetApp.getActiveRange();
}
/**
* Address
* Gets the address of a range in string format
* @param {Range} r a range
* @return {string} its address
*/
function Address(r) {
return r.getA1Notation();
}
/**
* WorkSheet
* Gets a sheet a range is on
* @param {Range} r a range
* @return {Sheet} a sheet
*/
function WorkSheet (r) {
return r.getSheet();
}
/**
* WorkSheetName
* Gets a the name of sheet
* @param {Sheet} ws a sheet
* @return {string} its name
*/
function WorkSheetName(ws) {
return ws ? ws.getName() : '' ;
}
/**
* vResize
* Resizes a range
* @param {Range} r a source range
* @param {number=} nr new number of rows (default as source)
* @param {number=} nc new number of columns (default as source)
* @return {Range} the resized range
*/
function vResize (r,nr,nc) {
if (( nr <= 0 && !isUndefined(nr)) || (nc <= 0 && !isUndefined(nc)))
return null;
else {
var rr = isUndefined(nr) ? r.getNumRows() : nr;
var rc = isUndefined(nc) ? r.getNumColumns() : nc;
return r.offset ( 0,0, rr,rc);
}
}
/**
* vOffset
* the offset of a range
* @param {Range} r a source range
* @param {number=} ro number of rows down from source range (default 0)
* @param {number=} co number of rows right from source range (default 0)
* @return {Range} the repositioned range
*/
function vOffset (r,ro,co) {
return r.offset ( fixOptional (ro,0), fixOptional (co,0) );
}
//Debug functions
/**
* VbaAssert
* An exception to throw on assert failure
* @constructor
* @param {string} what a message to display on failed assertion
*/
function VbaAssert(what) {
this.what = what;
this.toString = function () {
return 'VbaAssert: ' + this.what;
};
}
/**
* DebugItem
* Format an item for debugging purposes
* @param {*} a an item to be converted
* @return {string} a formatted version of the item
*/
function DebugItem(a) {
var t='';
if (isArray(a)) {
for (var i=0;i<a.length;i++ )
t+= (t ? ',' : '')+ (DebugItem(a[i]) );
return '[' + t + ']';
}
else if (IsObject(a)) {
t+=JSON.stringify(a);
}
else if (IsMissing(a))
t+= 'null';
else if (!a.toString) {
t += a? 'errordebugging' : '(null)' ;
}
else {
t+= a.toString();
}
return t;
}
/**
* DebugPrint
* Format an item for debugging purposes
* @param {...} var_args an array of items to log
* @return {string} a formatted version of the items
*/
function DebugPrint(){
Logger.log(DebugString(arguments));
}
/**
* DebugPrint
* Format an item for debugging purposes
* @param {...} var_args an array of items to format
* @return {string} a formatted version of the items
*/
function DebugString(){
var s ='';
for( var i = 0; i < arguments.length; i++ ) {
if (s) s += "|";
s+= DebugItem (arguments[i]) ;
}
return s;
}
/**
* DebugAssert
* throw an exception if not true
* @param {*} mustBeTrue value to assert
* @param {string} sayWhat additional message to to throw
* @return {*} the value to assert is returned
*/
function DebugAssert(mustBeTrue, sayWhat) {
if (!mustBeTrue)
throw new VbaAssert(sayWhat);
return mustBeTrue;
}
/**
* fixOptional
* throw an exception if not true
* @param {*} arg given value
* @param {*} defaultValue value to use if given value IsMissing
* @return {*} the new value
*/
function fixOptional (arg, defaultValue) {
if (isUndefined(arg) ){
DebugAssert(!isUndefined(defaultValue) ,
'programming error: no default value for missing argument');
return defaultValue;
}
else
return arg;
}
/**
* isUndefined
* Check if a value is defined
* @param {*} arg given value
* @return {boolean} true if undefined
*/
function isUndefined ( arg) {
return typeof arg == 'undefined';
}
// got this here
// https://javascriptweblog.wordpress.com/2011/08/08/fixing-the-javascript-typeof-operator/
/**
* toType
* get detailed type of javaScript var
* @param {*} obj given item
* @return {string} type
*/
function toType(obj) {
return ({}).toString.call(obj).match(/\s([a-zA-Z]+)/)[1].toLowerCase()
}
/**
* isTypeNumber
* Check if a value is a number type
* @param {*} arg given item
* @return {boolean} true if numeric type
*/
function isTypeNumber ( arg) {
return typeof arg == 'number';
}
/**
* isTypeString
* Check if a value is a string type
* @param {*} arg given item
* @return {boolean} true if string type
*/
function isTypeString ( arg) {
return typeof arg == 'string';
}
/**
* isArray
* Check if a value is an array
* @param {*} arg given item
* @return {boolean} true if array
*/
function isArray (arg) {
return toType(arg) == 'array';
}
/**
* makeKey
* used throughout to normalize strings for comparison
* @param {*} v given item
* @return {string} cleaned up string
*/
function makeKey(v) {
return LCase(Trim(CStr(v)));
}
// mimic a collection
// item is stored with a string key
// can be retireved either by its position or by string key
// automatically adjusted to match the base (1 for VBA)
/**
* VBA like collection
* @class
* @implements {collection}
* @param {number=} base base for constructor (default 1)
* @param {number=} cleanKey opt_argument whether to use makeKey on key values
* @return {collection} collection
*/
function collection(base,cleanKey) {
var pBase = fixOptional ( base , 1);
var pCleanKey = fixOptional ( cleanKey , true);
var pItems =[];
var pKeys ={};
var pLastUsed =-1;
var self = this;
/**
* Returns the base
* @this {collection}
* @return {number} the base for this collection
*/
this.base = function() {
return pBase;
};
/**
* Returns the items array
* @this {collection}
* @return {<Array>.*} the items in this collection
*/
this.items = function() {
return pItems;
};
/**
* Returns the number of items in the collection
* @this {collection}
* @return {number} the count of items in collection
*/
this.count = function() {
return pItems.length;
};
/**
* Returns the keys object for this collection
* @this {collection}
* @return {object} the keys object
*/
this.keys = function () {
return pKeys;
};
/**
* create a key for this item
* @this {collection}
* @return {string} a key
*/
this.generateKey = function () {
return makeKey(EGAHACKS.EGAHACKSCo + (++pLastUsed).toString());
};
/**
* return an item given its key
* @this {collection}
* @param {string|number} they key of the item to find
* @param {boolean=} opt_argument whether to complain if not found
* @return {*} the found item
*/
// -- item returns null or the item
this.item = function (k,complain) {
var x;
var y = isUndefined(x = self.index(k,complain)) ? null : pItems[x];
return y;
};
// -- swap - position swap for a and b - useful for sorting
/**
* swap the position of 2 items
* @this {collection}
* @param {string|number} they key of the first item
* @param {string|number} they key of the second item
* @param {boolean=} opt_argument whether to complain if not found
* @return {collection} the collection
*/
this.swap = function (a,b,complain) {
var xa = self.index (a, complain);
var xb = self.index (b, complain);
if (isUndefined (xa) || isUndefined(xb) ) {
// didnt find the swapees
return null;
}
// we dont know the keys for a & b so find them
var ka = self.findKey(xa+this.base());
var kb = self.findKey(xb+this.base());
DebugAssert (! (isUndefined(ka) || isUndefined(kb)), 'logic error in swap');
// swap the items
var t = pItems[xa];
pItems[xa] = pItems[xb];
pItems[xb] = t;
// repoint the keys
pKeys[ka]=xb;
pKeys[kb]=xa;
// all was good
return self;
};
// does a sort based on your comparison function
/**
* sort a collection
* @this {collection}
* @param {function(*,*)=} opt_argument a function that will do a comparison between 2 items
* @return {collection} the collection
*/
this.sort = function (yourNeedSwap) {
// provide a default comparison function
var swap = fixOptional(yourNeedSwap,function (a,b) { return (a>b) }) ;
// do the sort
for (var ita = 0; ita < self.count() -1 ; ita ++ ) {
for (var itb = ita ; itb < self.count() ; itb ++ ) {
if (swap(pItems[ita],pItems[itb])) {
self.swap(ita+self.base(),itb+self.base());
}
}
}
return self;
}
// -- add - returns null or newly added item
/**
* add an item
* @this {collection}
* @param {*} the item to add
* @param {string|number} they key to add
* @param {boolean=} opt_argument whether to complain if not found
* @param {*=} opt_argument not implemented yet
* @param {*=} opt_argument not implemented yet
* @return {*} the added item
*/
this.add = function (o,k,complain,before,after) {
// everything must have a key, so make one if not given
ks = isUndefined(k) ?
self.generateKey() :
(pCleanKey ? makeKey(k.toString()) : k.toString());
// see it it exists already
if (this.item(ks,false)) {
if (fixOptional(complain,true)) {
MsgBox ("item " + ks + " already in collection ");
}
return null;
}
else {
// add it to the end, and store the position in array against the key
var x = (pKeys[ks] = pItems.push(o) -1) ;
return pItems[x];
}
};
// -- index figures out the index number or undefined
/**
* get the index (position) of an item
* @this {collection}
* @param {string|number} the key of the required item
* @param {boolean=} opt_argument whether to complain if not found
* @return {number} the index of the item
*/
this.index = function (k,complain) {
// get the index of the item,either by checking the key or directly
// note that if the input is a number, it follows the base for the collection
// the returned value is the 0 based index into the pitems array
var x = isTypeNumber(k) ? k - pBase : pKeys[pCleanKey ? makeKey(k) : k];
if (isUndefined(x) ) {
if (fixOptional(complain,true)) {
MsgBox ("Requested item " + k + " not in collection");
}
}
return x;
};
/**
* get the key of an item from its index
* @this {collection}
* @param {string|number} the key of the required item
* @param {boolean=} opt_argument whether to complain if not found
* @return {number} the index of the item
*/
this.findKey = function (k, complain) {
if ( !isUndefined(x = self.index(k,complain)) ) {
for (c in pKeys) {
if (pKeys[c] == x) return c;
}
DebugAssert(false,'findkey couldnt find ' + k);
}
};
// -- remove returns the index or undefined if it didnt work
/**
* remove an item
* @this {collection}
* @param {string|number} the key of the required item
* @param {boolean=} opt_argument whether to complain if not found
* @return {number} the index of the item
*/
this.remove = function (k,complain) {
if ( !isUndefined(x = self.index(k,complain)) ) {
// remove from key object & decrement anything higher
for (c in pKeys) {
if (pKeys[c] == x){
delete pKeys[c];
}
else if (pKeys[c] > x) {
pKeys[c]--;
}
}
pItems.splice(x,1);
}
return x;
};
/**
* enumerate a collection
* @this {collection}
* @param {function(*,number)} a function that will be called for each item
*/
this.forEach = function(yourFunction) {
for (var i = 0 ; i < self.count() ; i++ ) {
if (yourFunction( self.item(i+pBase), i+pBase)) break ;
}
};
};
/**
* Enum for sorting.
* @enum {number}
*/
var ESORT = Object.freeze(
{'ESORTNone':1000,
'ESORTAscending':1001,
'ESORTDescending':1002}
);
/**
* Enum for constant identifiers.
* @enum {string}
*/
var EGAHACKS = Object.freeze(
{'EGAHACKSCo':'~g~',
'EGAHACKSTimer':'~t~'}
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.