Search code examples
javascriptjqueryexcelvbascriptcontrol

Convert Recursive jQuery For each loop into JavaScript for VBA Script Control


I'm trying to use the following script to iterate through a nested JSON object. It works fine in html, however, I'm calling it from a Excel VBA ScriptControl object. When I do, it returns an "undefined" error for the "$"

I'm assuming it's because the VBA ScriptControl does not allow for jQuery (is that true?)

So... Can some help in converting the "$.each()" parts below into javascript?

Code:

function jsonFunction() {
    var jsonObj = {
        Item1: 'This is a test',
        Item2: { Value1: '100', Value2: '200' },
        Item3: { Value1: '101', Value2: '201' },
    };

    var result = '';

    $.each(jsonObj, jsonLoop);

    function jsonLoop(key, value) {
        if (typeof value === 'object') {
            $.each(value, jsonLoop);
        } else {
            result += key + ' : ' + value + ', ';
        }
    } 
    return result;
}

Solution

  • You could just create your own each function

    function each(obj, cb) {
        for (var key in obj) {
            cb(key, obj[key]);
        }
    }
    
    function jsonFunction() {
        var jsonObj = {
            Item1: 'This is a test',
            Item2: { Value1: '100', Value2: '200' },
            Item3: { Value1: '101', Value2: '201' },
        };
    
        var result = '';
    
        each(jsonObj, jsonLoop);
    
        function jsonLoop(key, value) {
            if (typeof value === 'object') {
                each(value, jsonLoop);
            } else {
                result += key + ' : ' + value + ', ';
            }
        } 
        return result;
    }