Multithreaded VBA – An Approach To Processing Using VBScript

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

Comments (26)

VBA IF Function – Using IF, ELSE:, ELSEIF, IF THEN in VBA code

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

Comments (12)

Treemap in Excel – Coming Soon

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

Comments (12)

VBA Select Case – Using VBA Select Case Statement in Excel

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

Comments (8)

Control Chart in Excel Using VBA – Six Sigma Control Chart Code & Software

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

Comments (5)

Slicer VBA Code – Create, Change or Modify a Pivot Table Slicer using VBA

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

Comments (7)

Memory Game – Pattern Matching

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

Comments (1)

HTML to Excel using Javascript and Excel to HTML using VBA

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

Comments (13)

VBA Code – Multiple Column Text to Column Conversion

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

Comments (1)

Pivot Table VBA: VBA to Read & Modify a Pivot Table in Excel

Posted on 11 September 2009

Here is a collection of VBA codes to work with Pivot Tables: 1. Create a pivot table using a VBA Macro 2. Delete all Pivot Tables Using a VBA Macro 3. Refer to a Pivot Table directly within a VBA Macro 4. Refer to a Pivot Table Field directly within a VBA Macro 5. Refer to a Pivot Item of a Pivot Field present in a Pivot Table directly using a VBA Macro 6. Remove old (ghost) Items from ... Continue Reading

Comments (1)

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)

Excel Slow to Respond – Avoiding Mistakes that Make Excel Slow Down to a Crawl

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

Comments (9)

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 (13)