Interactive Chart in VBA using Mouse Move Event

Posted on 02 September 2009

VBA can be used to make interactive charts that respond to mouse movements. In this article we first start of by making a simple bar chart embedded in a chart sheet and then use the mouse move event of the chart sheet to make it interactive. Create a simple Bar Chart For the purpose of this exercise, we take the list of top 15 billionaires and sort it based on their networth. We ... Continue Reading

Comments (21)

VBA Read List and Combine All Excel CSV Text files in a Folder or Path

Posted on 29 August 2009

Here's the VBA code to read, list and combine all excel, csv, text any other format file in to a single Excel sheet. By editing the code, you can make the program read and then combine files of only a specified extension say, Excel (.xls) or comma separated (.csv) or text (.txt). If you have a lot of small files getting FTP'd over or getting accumulated in a location, this can save a lot of time. [cc lang="vb"] Sub List_All_The_Files_Within_Path() Dim File_Path As ... Continue Reading

Comments (7)

VBA code for Text to Column

Posted on 26 August 2009

Here is a basic VBA code for converting Text to Column that works across multiple columns in an Excel workbook. You can edit this code to turn on (or off) various options such as DataType (xlFixedWidth or xlDelimited), TextQualifier and which delimiters to use while converting from text to column. [cc lang="vb"] Sub text_to_column() Application.ScreenUpdating = False On Error Resume Next For Each wksht In ActiveWorkbook.Worksheets For Each col In wksht.Columns Columns(col.Column).TextToColumns _ ... Continue Reading

Comments (9)

VBA Message Box (msgbox) – The Message Can Do Better

Posted on 12 August 2009

The VBA message box (msgbox) helps convey a pre-defined messages to a user. Developers often use the message box for stuff ranging from debugging vba (msgbox "I am here" types) to prompting users for action (msgbox ("Sorry, what you asked for could not be done", vbAbortRetryIgnore)). While it remains one of the most useful and frequently used commands in vba, it is also common to find developers being stuck with the most primitive form of the message box and losing ... Continue Reading

Comments (12)

String Comparison Function in VBA

Posted on 30 July 2009

Here's a look at a few string comparison functions in VBA : Types of string comparisons in VBA Binary String Comparison (Case sensitive) in VBA For any formula If you want to compare two string in such a manner that each individual characters is compared with its counterpart in a case sensitive manner (Ex. “This” is not equal to “this” because ‘T’ is not equal to ‘t’), you can do either of the two things: 1. Declare the statement Option Compare Binary at ... Continue Reading

Comments (3)

How to write a macro in Excel

Posted on 29 July 2009

How to write a macro in Excel? Here are a few quick tips: Excel Macro - How to Record Excel Macro - How to Edit Excel Macro - Improving speed of code Excel Macro - Examples Recording a macro in Excel How to record a macro in Excel 2007 and Excel 2010 In Excel 2007 (and Excel 2010), the "Record Marco" menu option is under the "View" ribbon. Click on the "Macros" button towards ... Continue Reading

Comments (7)

VBA Chart

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

Comments (7)

Make Mouse Scroll Wheel Work in VBA Editor (IDE)

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

Comments (12)

How to hide a sheet in Excel

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

Comments     

VBA For Loop – For Next and For Each In Next

Posted on 01 July 2009

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)

Fix Runtime Error in VBA

Posted on 24 June 2009

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)

Excel VBA For Do While and Do Until Loop

Posted on 21 April 2009

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)

Sudoku solver in Excel VBA

Posted on 23 August 2008

Update: You can download the Sudoku Solver in Excel with brute force here (latest version) here. For the uninitiated, Sudoku is a grid of 9x9 cells where each cell can and must have a value from 1 to 9 such that no number must be repeated in a row, column or any of the 9 groups of 3x3 grids. And not onto the circumstances behind this sudden urge to develop a sudoku solver. ... Continue Reading

Comments