Posted on 25 January 2010
Today's guest post is authored by Daniel Ferry, a longtime professional Excel developer and consultant (and a newbie blogger). A few months ago, I wrote a post on how one can
simulate multithreading in VBA using Excel. Daniel picked up a thread from there and commented that he had some good success in marshalling some of windows multithreaded subsystems from within VBA. In this article he describes his approach.
Multithreading VBA - Using VBScript
Try as we might to work around ...
Continue Reading
Posted on 01 January 2010
The IF function in VBA is one of the most frequently used of all statements. The IF function checks if the specified condition is being met or not. The IF function in VBA works in a slightly different manner from how it works in Excel. In Excel, the IF function does two things : It checks whether the given condition evaluates to TRUE or FALSE and then returns a value to use based on the evaluation i.e IF(condition, return_value_if_condition_true, ...
Continue Reading
Posted on 24 December 2009
Its been a few days since I posted. In case you were wondering what I was up to - yours truly was busy coding a program to create treemaps in Excel. Treemaps are interesting creatures. You can pack a few thousand data points in a single treemap and utilize just about the same space as any of the traditional charts. The term 'treemap', as we see it being used nowadays, actually refers to the inward growing members of the treemap ...
Continue Reading
Posted on 20 December 2009
The VBA Select Case Statement is an important construct in the VBA language. The Select Case statement in VBA allows the program to execute one set of statements out of many based on what a given expressions evaluates to. In today's post we will look at some basic and intermediate ways in which it can be used when writing VBA code.
Basic Form of the VBA Select Case Statement
[cc lang="vb"]
Select Case expression_to_test
Case expression_value_1 : Code to Execute When expression_to_test = expression_value_1
Case ...
Continue Reading
Posted on 16 December 2009
In
Six Sigma Control charts using Excel we saw how to make
control charts using named ranges. From there on, it was a short hop to porting the logic to VBA. So here is the VBA code for automating the creation of a six sigma control charts. It has been tested with Excel 2000, Excel 2007 and Excel 2010.
How to Use the Control Chart Program
1. Simply click the 'Make Control ...
Continue Reading
Posted on 23 November 2009
Here is some VBA code that can help you automate working with a
slicer in Excel. Slicers, as you would know, are the latest introduction to Excel. As I mentioned in my previous post, they can also be programattically controlled using simple VBA code. Let's see how.
Slicer Creation using VBA - Basic Steps
Let's look at the slicer hierarchy - a workbook has slicer caches (accessed the SlicerCaches object), each slicer cache in turn having multiple slicers (one for each ...
Continue Reading
Posted on 10 November 2009
I saw this memory game a few days back. The website promised an instant boost in the players memory if they played this game for a 4 week period. So if you've been wondering how to get that 'excel'-ent memory, here's the prefect game for you.
The Memory Game
The game is pretty straightforward. You are shown a grid of cells, some of which are colored and some are left blank. Once you've memorized the pattern of the colored cells, the ...
Continue Reading
Posted on 23 September 2009
This two part article consists of:
Part A -
Converting HTML to Excel using Javascript
Part B -
Exporting Excel to HTML using VBA
HTML to Excel using javascript
Let’s first take a look at tables in html. Data tables are typically stored in HTML using the <table> tag. Such a table will typically consist of one or more rows (<tr> tag) with each row having one or more cells (<td> tag). To read a table from an HTML document ...
Continue Reading
Posted on 21 September 2009
Here's a bit of VBA code to convert
Text to Column which will work across multiple columns selected together at one go.
...
Continue Reading
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 06 August 2009
Everyone hates it when excel becomes slow to respond. So here are some tips to avoid doing things that can slow down excel. Excel 2007 and later versions sport a much bigger grid which tends to amplify poor spreadsheet design. An Excel spreadsheet that seemed a bit slow in Excel 2000 would likely get bogged down to a crawl in Excel 2007 if you, like me, are using the same hardware.
The key to understanding what makes excel slow lies ...
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