Posted on 20 July 2009

Making a chart using VBA can be easy. VBA provides a handle to a chart object using the "ChartObject" class and to the chart data series trough the "Series" class. Let's look at a basic example where we use VBA to create an empty chart to our spreadsheet and then add data to it.
[cc lang="vb"]
Sub CreateChart()
Dim ChartObj As ChartObject
Dim ChartSeries as Series
Set ChartObj = ActiveSheet.ChartObjects.Add ( Left: = 100, Width: = 550, Top: = 75, Height: = 325)
Set ...

Continue Reading
Posted on 15 July 2009

Last week my computer broke down. This time, unlike previous occasions, it just wouldn't resurrect itself. That rig was my first build and had been with me for the past eight years. It wasn't a thoroughbred, a humble Duron (AMD) at heart but it had its fair share of adventures. All those impromptu college parties, those last minute project reports, getting hauled around in a rug-shack, a thousand tweaks to squeeze the last remaining MHz out of the processor, even ...

Continue Reading
Posted on 14 July 2009

This ranks right at the top of annoyances when it comes to working with the VBA IDE - the mouse scroll wheel simply does not work. So if you have more lines of code than can fit into a single screen in the VBA IDE, you end up having to drag the scroll bar to up/down to navigate. Jimmy Peña recently posted this

very useful VBA tip on his

informative blog that provides the solution to this issue. ...

Continue Reading
Posted on 12 July 2009

How to Hide a sheet from users temporarily ?
This option works only if you want Excel to hide the sheet temporarily - subsequent users can always make the worksheet visible again anytime they wish to. To make a worksheet invisible, simply click on the ‘Format’ -> ‘Sheet’ ->’Hide’ option from the menu and it will hide the sheet from view. This is oftentimes the simplest option.

How To Hide a sheet with restricted ...

Continue Reading
Posted on 08 July 2009

Array formulas help you turn normal Excel Formulas into super formulas. Here are a few examples of

Array formulas. These examples highlight the use of multiple conditions within a single array formula and how we can club multiple formulas in to a single array formula.
Array formulas are powerful - no doubt about that. However before we go any further, one must look at the flip side to using array formulas. They take much more computational power and can slow ...

Continue Reading
Posted on 06 July 2009

A Javascript charting library is a compilation of javascript code which helps you create charts in a browser. Here's a list of 5 Javascript charting libraries that I had a chance to review :
(Each of the Javascript charting libraries reviewed below would offer its own custom functions to interact with the chart object. If a chart fits your need, you may want to read the chart library's API documentation in greater detail. However, if you're short on development time, this ...

Continue Reading
Posted on 05 July 2009

Array Formulas in Excel
An Array Formula in excel is a formula that uses arrays instead of single cell value as input.

Excel Array Formulas can be thought of as many formulas packed into a single super formula. In this article we will take a look at the syntax of an array formula, its various parts, learn how to write a basic array formula and then graduate on to writing the more powerful versions of it. Before we begin, let ...

Continue Reading
Posted on 04 July 2009

IF formula in Excel checks whether a given condition evaluates TRUE or FALSE.

Syntax of IF Formula
Example of IF Formula
Possible Errors returned by the IF Formula
IF Formula Syntax
IF Formula has three parts:
IF(criteria, value_when_true, value_when_false)

criteria
criteria is the condition that we would like to evaluate for being TRUE or FALSE.
value_when_true
value_when_true is the value that will be returned by the function when the above criteria evaluates to TRUE. This ...

Continue Reading
Posted on 03 July 2009

To get the current path using javascript without the %20 and / (backslash), use the following code:
[cc lang="vb"]
var path = document.location.pathname;
alert (path);
[/cc]
However using the javascript path variable in this manner gives something like this:

So now we use the string replace function in Javascript to rectify this:
[cc lang="vb"]
var path = document.location.pathname;
path = path.replace(/\//gi, "");
path = path.replace(/%20/gi, " ");
path = path.replace(/\\/gi, "\/");
alert (path);
var path = document.location.pathname;
[/cc]
This javascript code gives us the path ...

Continue Reading