Learn VBA & Macros in 1 Week!

JavaScript - Issue Calculating

Full Excel VBA Course - Beginner to Expert

Issue Calculating	View Content

Hello everyone. Below is code I have developed for a simple spreadsheet. The issue I am having is that when I total a row or column using "=sum(a1:a5)" and then I try and total another row and add the sum of the two totals together I am not getting a valid response back. All I am presented with is a "0" in the column cell. Here is the code in two files and have also included an attachment of the whole project:

tablepage.js
Code:

// JavaScript Document

var tblRows = 20;
var tblColumns = 10;
var tblArray = new Array(tblRows);
var p = window.parent;

// Initial page event handler
function initPage() 
{
    p.refAssignCellFromTextbox = assignCellFromTextbox;

    for (var i = 0; i < tblArray.length; i++) 
	{
        tblArray[i] = new Array(tblColumns);
        for (var j = 0; j < tblArray[i].length; j++)
            tblArray[i][j] = "0";
    }

    recalculate();
}

// Recalculate cell values
function recalculate() 
{
    for (var i = 0; i < tblRows; i++) 
	{
        for (var j = 0; j < tblColumns; j++) 
		{
            if (tblArray[i][j].indexOf("=SUM") != -1) 
			{
                calculateCell(i, j);            
            }
        }
    }
}

// Click cell event handler
function clickCell(cellRef) 
{
    thisRef = cellRef;
	
    var cellID = cellRef.id;

    var row = parseFloat(cellID.substr(0, 3)) - 1;
    var column = parseFloat(cellID.substr(3, 2)) - 1;

    var cellValue = tblArray[row][column];
    var tokenArray = getFormula(cellValue);

    if (tokenArray != null)
        p.assignTextboxFromArray(cellValue);
    else
        p.assignTextboxFromCell(cellRef);
}

// Update cell from textbox
function assignCellFromTextbox(tblValue) 
{
    if (thisRef != undefined) 
	{
        if (tblValue == "") 
		{
            tblValue = "0";
        }
        var tokenArray = getFormula(tblValue);

        if (tokenArray != null) 
		{
            assignArray(thisRef.id, tblValue.toUpperCase());
        }
        else 
		{
            if (!isFloat(tblValue)) 
			{
                    parseValue = tblValue.replace(/[^0-9]/g, '');
                    tblValue = parseValue;
            }
            assignArray(thisRef.id, tblValue);
            if (tblValue == "0")
                thisRef.innerText = "";
            else
                thisRef.innerText = tblValue;
        }
		
        recalculate();
    }
}

// Determines if a user entered a formula
function getFormula(tblValue) 
{
    var pattern = /[:|\(|\)]/;
    var ar = tblValue.split(pattern);
    var sum = ar[0].toUpperCase();

    if (ar.length < 3)
        return null;
    else if (sum != "=SUM") 
	{
        return null;
    }
    else 
	{
        return ar;
    }
}

function assignArray(cellID, tblValue) 
{
    var row = parseFloat(cellID.substr(0, 3));
    var column = parseFloat(cellID.substr(3, 2));

    tblArray[row - 1][column - 1] = tblValue;
}

function calculateCell(row, column)
{
    var tokenArray = getFormula(tblArray[row][column]);

    if (tokenArray != null) 
	{
        var fromColumn = tokenArray[1].substr(0, 1);
        var fromRow = tokenArray[1].substr(1, tokenArray[1].length - 1);

        var toColumn = tokenArray[2].substr(0, 1);
        var toRow = tokenArray[2].substr(1, tokenArray[2].length - 1);

        var fromRowIndex = parseFloat(fromRow) - 1;
        var fromColIndex = fromColumn.charCodeAt(0) - 65;

        var toRowIndex = parseFloat(toRow) - 1;
        var toColIndex = toColumn.charCodeAt(0) - 65;

        var sumTotal = 0;

        for (var i = fromRowIndex; i <= toRowIndex; i++) 
		{
            for (var j = fromColIndex; j <= toColIndex; j++) 
			{
                if (isFloat(tblArray[i][j]))
                    sumTotal += parseFloat(tblArray[i][j]);
            }
        }

        var cellID = fillField((row + 1).toString(), 3) + fillField((column + 1).toString(), 2);
        document.getElementById(cellID).innerText = sumTotal;
    }
}

function isFloat(s) 
{
    var ch = "";
    var justFloat = "0123456789.";

    for (var i = 0; i < s.length; i++) 
	{
        ch = s.substr(i, 1);
        if (justFloat.indexOf(ch) == -1)
            return false;
    }
    return true;
}

function fillField(s,n)
{
    var zeros = "0000000000";
    return zeros.substring(0, n - s.length) + s;
}

spreadsheet.js
Code:

// JavaScript Document

var refAssignCellFromTextbox;
var nCharsAllowed = "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890.=(): ";


// Event Handler
function initPage() 
{
    document.getElementById("TableFrame").style.top = "50px";
    document.getElementById("TableFrame").src = "tablepage.html";
}

// Updates text field when a user clicks on a particular field from with the spreadsheet
function assignTextboxFromCell(cValue) 
{
    document.getElementById("fieldEntry").value = cValue.innerText;
    document.getElementById("fieldEntry").focus();
}

// Assists tablepage.js with updating a value from a 2D array
function assignTextboxFromArray(aValue) 
{
    document.getElementById("fieldEntry").value = aValue;
    document.getElementById("fieldEntry").focus();
}

// Used when a user presses a key to update text field
function filterText() 
{
    if (window.event.keyCode != 13) 
	{
        if (!nCharOK(window.event.keyCode))
            window.event.keyCode = null;
    }
    else 
	{
        window.event.keyCode = null;
        refAssignCellFromTextbox(document.getElementById("fieldEntry").value);
    }
}

// Checks to see if character entered falls within our preset
function nCharOK(c) 
{
    var ch = (String.fromCharCode(c));
    ch = ch.toUpperCase();

    if (nCharsAllowed.indexOf(ch) != -1)
        return true;
    else
        return false;
}

// Clears all data from the screen
function clearPage() 
{
    initPage();
    document.getElementById("fieldEntry").value = "";
}

// Add a row to the table
function addRow(frmTable) 
{
	var table = document.getElementById(frmTable);
	var rowCount = table.rows.length;
	var row = table.insertRow(rowCount);
	var cell1 = row.insertCell(0);
	// cell2.innerHTML = rowCount + 1;
	// Unless this is going to be completely dynamic
	// the rest of the table code would have had to be 
	// entered in statically. Not happening.
}

// Delete a row from the table
function deleteRow(frmTable) 
{
	try 
	{
		var table = document.getElementById(frmTable);
		var rowCount = table.rows.length;

		for(var i=0; i<rowCount; i++) 
		{
			var row = table.rows[i];
			var chkbox = row.cells[0].childNodes[0];
			if(null != chkbox && true == chkbox.checked) 
			{
				table.deleteRow(i);
				rowCount--;
				i--;
			}
		}
	}
	catch(e) 
	{
		alert(e);
	}
}

Full Excel VBA Course - Beginner to Expert

Calculating Bmi

Similar Tutorials

JavaScript - Issue Calculating

Issue Calculating

Similar Tutorials

Calculating Bmi

Adding Up, Calculating Value

Calculating Time

Calculating A Person's Age

Calculating Shipping With If/else And Function

Help With Calculating Order Form

Forms, Calculating, Formulas .. Help?

Why Is My Countdown Not Calculating Correctly?

Calculating Time Differences

Lost Calculating Vat Using Javascript

Calculating The Output Of Functions Together

Calculating Form Totals

Calculating The Difference Between Two Dates

Calculating Age From Date Of Birth In Javascript?

Problems Calculating Total In Form

Help With Calculating The Total Of Fields On A Web Page

Calculating A Number Through 4 Drop Down Menus

Calculating Fees In Percentage In A Form

External Javascript Calculating Wrong Form

Help With Calculating Shopping Total With A Quantity Textbox