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