How to hide a sheet in Excel

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 ...

Continue Reading

How to hide a sheet in Excel

VBA For Loop – For Next and For Each In Next

Posted on 01 July 2009

VBA For Loop - For Next and For Each In Next
The For Loop in VBA is one of the most frequently used loops in VBA. The For loop has two forms: For Next and For Each In Next. The For loop is typically used to move sequentially through a list of items or numbers. To end the for loop at any given point we can use the exit for statement. Let's take a closer look at each of these loops. ...
Continue Reading

Comments (29)

How To Create Pivot Table Using VBA

Posted on 28 June 2009

How To Create Pivot Table Using VBA
How to create a pivot table using VBA? Assuming that the data for the pivot table is laid out like this, then all you need to create a pivot using vba are there four lines of code: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= ActiveSheet.UsedRange).CreatePivotTable TableDestination:="", TableName:= "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1) ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Name" ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").Orientation = xlDataField The pivot table so created using the VBA code above can be ...
Continue Reading

Comments (11)

Fix Runtime Error in VBA

Posted on 24 June 2009

Fix Runtime Error in VBA
To fix runtime errors is VBA, use the following three statements - On Error, Resume and Debug. The first one traps a runtime VBA error when it occurs, the second one tells the application the point from which to resume code execution when the error has been caught (if you decide to catch the error) and the third one informs the user where (and which) runtime error took place. A runtime Error in VBA can occur for a plethora of reasons. ...
Continue Reading

Comments (7)

How to synchronize two or more pivot tables using VBA

Posted on 08 June 2009

How to synchronize two or more pivot tables using VBA
One often comes across situations when one wants to synchronize two or more pivot tables. The problem arises when two or more pivot tables have the same underlying data set but show different views to the user. In such cases, combining both of them without losing data separation is not really an option. I wrote this small piece of VBA code a few months back that can help synchronize pivots across ...
Continue Reading

Comments     

Sudoku Solver Code in Excel with Brute Force – Solver Code, Logic and Program

Posted on 11 May 2009

Sudoku Solver Code in Excel with Brute Force - Solver Code, Logic and Program
This Sudoku solver in Excel will help you solve a sudoku puzzle with brute force. This Sudoku solver, has been written in VBA and incorporates a brute force algorithm which helps its solve some of the toughest sudoku puzzles using nothing more than an excel spreadsheet. You can download the Sudoku Solver in Excel with Code, Logic and Algorithm here. In this article we will talk about how to develop a Sudoku solver using code in Excel, discover the tips ...
Continue Reading

Comments (5)

Excel VBA For Do While and Do Until Loop

Posted on 21 April 2009

Excel VBA For Do While and Do Until Loop
The VBA For Loop and the Do While loops are the two most important loops in the VBA language. They can be used to carry out a specific task in an iterative manner till such time that a condition is met (or the VBA loop is terminated). Download the worksheet with examples of the for, do while and do until loops in VBA The VBA For Loop The VBA For ...
Continue Reading

Comments (7)

Export Excel as Fixed Width Text File Format using VBA

Posted on 01 April 2009

Export Excel as Fixed Width Text File Format using VBA
Although the reverse is easy, export of an excel worksheet as a fixed width text file (.txt) file is sometimes is not. Now, Excel does provide you with the option to save a worksheet as a text (.txt) or a Comma Separated (.csv) file, but if you had to ensure that each of the fields have their own specified lengths, this job is far from easy. I recently came across a ...
Continue Reading

Comments (70)

How to speed up calculation and Improve performance of Excel and VBA

Posted on 23 November 2008

How to speed up calculation and Improve performance of Excel and VBA
The key to increasing speed of calculation and improving performance of your excel worksheet and VBA code lies in understanding how Excel calculates (and re-calculates) each cell and resolves dependencies. Whenever an event occurs that causes Excel to recalculate (for example a cell is changed, a VBA code calls for re calculation or anything else), the entire calculation process is carried out roughly as follows: 1. Load workbook in memory when the workbook is first opened. 2. Track all dependencies and build ...
Continue Reading

Comments (18)

Simulating Multithreading in VBA using Excel

Posted on 08 November 2008

Simulating Multithreading in VBA using Excel
This article describes 3 ways of simulating multithreading in VBA using Excel. One feature that I sorely miss in the VBA's integrated development environment (IDE) is the ability to do multithreading. I think part of that comes from having worked in my previous avatar with Java and C++. In the past few days I’ve been thinking about ways in which one can ‘simulate’ multithreading in excel. (If you noticed, I said ‘simulate’ – you can’t actually multithread with vba – not atleast ...
Continue Reading

Comments (7)

How to Read Data from Pivot Table Using VBA in Excel

Posted on 07 November 2008

How to Read Data from Pivot Table Using VBA in Excel
Reading pivot tables using vba can be quite easy. Here are a few ways you can do it. You may also want to read my previous article on formatting pivots. (Before you dive further, you may want to download this example which will guide you through some of the steps in reading a pivot table data using vba) The Pivot Table Hierarchy Lets look at this peice of code: Sub ListAllItemObjects()...
Continue Reading

Comments (20)

3 Nifty Ways to Send Email Using VBA in Excel

Posted on 21 October 2008

3 Nifty Ways to Send Email Using VBA in Excel
This article shows three methods using which you can send email using VBA in Excel. You can download a working example of sending email using vba in Excel here. Excel allows you to create the best-in-class charts, work with large amount of data and data sources work with images, flowcharts and what not. And if that was not enough, you can use the inbuilt vba editor to automate sending emails from ...
Continue Reading

Comments (46)

How This Bear Market Compares – NY Times

Posted on 13 October 2008

How This Bear Market Compares - NY Times
A problem frequently encountered with displaying multiple series with overlapping data points within a single line chart is that they just seem to entangle themselves. To get an idea of all the data point that a series represents, the user has to either mentally segregate it from the rest or click on it to highlight the particular line. The NY Times recently published a chart How This Bear Market Compares that tries to solve this problem by highlighting ...
Continue Reading

Comments (4)

Get each of your spreadsheets a customized menu

Posted on 09 October 2008

Get each of your spreadsheets a customized menu
Those who’ve worked with elaborate multi-worksheet dashboards would realize the usefulness of adding a customized menu option. Apart from other things, a customized menu option can be used to guide the user to specific portions of an excel spreadsheet. For example, if you have a long report consisting of multiple charts, pivots, images and data fields, you may want to spare the reader the effort of locating the most useful (or important) portions by creating a new menu option and ...
Continue Reading

Comments (1)

The $700 billion and the PLAN

Posted on 03 October 2008

The congress and the house have approved the $700 billion rescue plan. However for those who had a chance to read through the fine print, there was disappointment in store….there’s wasn’t any plan. The proposal itself is actually a lose aggregation of definitions and statements of intent aimed at keeping the homeowners in their houses. The document passed by the senate simply lays down the guidelines for providing the Secretary of the Treasury powers to use $700 billion from the ...
Continue Reading

Comments     

How much is $700 billion?

Posted on 03 October 2008

How much is $700 billion?
USA Today came out with this graphic recently which compares the size of the recent bailout package with the other historical packages in the past. I tried my own version of this and came pretty close to achieving the same results in excel. "Close" because there would be errors since the size of the boxes may not accurately represent the number corresponding to it. As Paulson said, "If you have ...
Continue Reading

Comments     

Making pivot tables move in tandem

Posted on 23 September 2008

Making pivot tables move in tandem
While working with multiple pivot tables, one often encounters a situation where multiple pivot tables are created with the same underlying set of data. In such cases, you may want to have the ability to simply select a value from a single drop-down and have all the pivot tables reflect that change. A small piece of code can be used to achieve this. It uses the PivotTable.PivotFields().PivotItems attribute to check if the value selected ...
Continue Reading

Comments (2)

You’ve seen 2D, what about 3D simulation in excel

Posted on 20 September 2008

You've seen 2D, what about 3D simulation in excel
Peter Rakos in his article in Gamasutra shows how to create a 3D simulation in Excel. He uses the create polyline (Shapes.AddPolyline) function in excel to generate a set of  triangles (2D). A paid of these triangles is used to generate one surface of a rotating cube. 12 such triangles are used to generate the entire cube (12 triangles = 6 surfaces = 1 cube). Before each iteration, the previous shapes are destroyed ...
Continue Reading

Comments     

Speed Gauges – An absolute No No

Posted on 11 September 2008

Speed Gauges - An absolute No No
.....because they take up a lot of space to show just one data element. However, if you still want to go ahead, you may want to take a look at this. (Inspired by the speed gauge depicted on BusinessObjects, now a SAP company). You can download the file here.
Continue Reading

Comments (2)

Tracking mouse movements in Excel

Posted on 11 September 2008

Tracking mouse movements in Excel
I wrote this small program that tracks the movement of the mouse in excel. The program uses an image to return the mouse X,Y coordinates. The shape that follows the mouse changes its attributes as it passes through various points in space. You can download the file here.
Continue Reading

Comments     

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel


ARCHIVES