Category Archives: VBA

Excel VBA Shortcut Range References

When working in VBA we often need to refer to a range of cells. What’s the best way to do so?

Well that’s a loaded question! It totally depends on your objective. As with so many things in Excel there are a zillion different ways to refer to a range from VBA. In many circumstances I prefer to use the shortcut brackets, [].
shortcut.png
Now I will warn you upfront that the shortcut method is never the quickest, but for me the clarity and simplicity it provides usually wins out. And truth be told, the speed difference is modest. If I need to provide the quickest execution speed possible, I’ll chose a different approach. But most of the time the tiny speed difference is irrelevant.
The most common method that I see used to set the value of a cell from VBA is this:
Range("A1").Value = 123
The shortcut method is this:
[A1] = 123
Just remember that the square brackets are a replacement for the Range/Parentheses/Quotation Marks construct. I guess phrasing it that way highlights the brevity of the shortcut.
The method returns a real reference to the range, so just about anything that can be done with a normal range reference can also be done with the shortcut reference. It can be used on either side of the equal sign. It can be used to feed other functions. And it has all of the methods and properties of a normal range.
To be fair I cheated a little in the above comparison. But to demonstrate that shortcut references have the properties of a normal range, we could have typed it like this:
[A1].Value = 123

What is inside the square brackets can be a normal A1 style range reference, but it can also be a defined name. For example, if the workbook contained a Named Range called MyRange, then:
MsgBox [MyRange].Address
would display a message box with the address that defines the range.
[a:a]

is a reference to all of column A.

[7:7]

is the range defined as all of row 7.

[r14, d20:g30, MyRange, a1].ClearContents

deletes the values from four distinct ranges all at once.

[h:h 5:5] = "hello world"

references the intersection of two ranges to set the value of H5 to a familiar text.

Set vArr = [CurRow CurCol].CurrentRegion

sets the variant array, vArr, equal to all of the cell values in the currently used rectangular range surrounding the intersection of the named ranges, CurRow and CurCol. If thosed named ranges were built from dynamic named formulas then such a construct would be potent indeed. vArr will always be a 2-D variant array, with the rows from the range as the first index, and the columns as the second index.

Now when the shortcut is used on the left side of an equal sign, method, or property assignment, it is important that it reference a range. For example:
[some_named_range] = lngVariable
If the named formula some_named_range references a range on a worksheet, even if a formula is used to produce it, then this will work. On the other hand, if some_named_range references a constant value (from a formula or from a named constant) then the above notation will raise a Run-time error.
I agree with this behavior in the case of a value returned by a named formula, but a named constant should be able to have its value set in this way, in my opinion. But obviously other range properties would not be accessible in the case of a named constant (such as .Address, or .Font, etc.) and this is probably why you cannot change a named constant this way.
The most common usage of the INDEX worksheet function returns a value, not a range like OFFSET does. So if some_named_range was built using INDEX the common way in the name manager, then it is a value and would fail in the above assignment. If it were instead built on OFFSET, then it would work. INDEX can be used to return a range, but that is a story for an upcoming post.
If some_named_range referred to the range of 1,000 cells then:
[some_named_range] = 5
would place a 5 in all 1,000 cells.
In my Animated Business Chart, the AnimateChart sub contained these two lines:
Names.Add "old", [chart_values]
Names.Add "switch_to_record", [record_index].Value
which deserve some explanation.
Names.Add is how to create (or alter an existing named range/formula) from VBA. The first parameter to the method is the name to be applied the named range/formula. The second parameter is the value or values or formula the defined name should refer to. If the name exists when this code runs, it will be replaced by whatever is in the second parameter. There is no way to edit an existing name directly, nor is there a need to delete the existing range first.
Now why does the first line use for the second parameter a range reference, while the second line uses the Value property of a range reference?
Here’s why. [chart_values] is a named formula that returns a set of values and not a range, so it is impossible to use the .Value property on it. As an aside, I usually wouldn’t anyway as the .Value property is the default property of any range reference, and it offers no advantage in speed or clarity. In fact, as you can see in the table below, .Value is usually SLOWER.
Ah, but then why do I use the .Value property for the [record_index] named range in the second line? The answer is a little subtle, but is critical to the successful animation of the chart. The second line is defining a named constant, and it should have a hard number like 3 as its defined value. On the other hand, [record_index] is a named range pointing at a cell on the worksheet. If we dropped the .Value, we would be setting [switch_to_record] to the same cell reference, and NOT to a hard constant value. Using the .Value property ensures that it is set to the hard number and not the range reference.
So how modest is the speed penalty for using this shortcut? I timed a number of different ways of referencing a range, using the GetTickCount library which is accurate to the millisecond. For each test I closed Excel and freshly opened the testing workbook to ensure the memory was clear and that each method was operating under the same conditions. For the first round of tests, I set a range to a value 100,000 times in a For/Next loop, where “i” was the looping variable.
Here are the results:
 Method  Seconds  Writes/Second
 Set o = Sheet1.Range(“a1″), o = i  21.672  4,614
 Cells(1,1) = i  22.297  4,484
 Set o = Sheet1.Range(“a1″), o.Value = i  23.671  4,224
 Range(“a1″) = i  23.812  4,199
 Inside: With Sheet1: .Range(“a1″) = i  23.961  4,181
 Range(“a1″).Value = i  24.594  4,066
 Range(“a_named_range”) = i  24.703  4,048
 Sheets(1).Range(“a1″) = i  25.843  3,869
 Sheet1.Range(“a1″) = i  25.938  3,848
 Worksheets(“Sheet1″).Range(“a1″) = i  26.141  3,825
 Names.Add “a_named_constant”, i  26.906  3,716
 Worksheets(1).Range(“a1″) = i  28.468  3,512
 Sheets(“Sheet1″).Range(“a1″) = i  29.031  3,444
 [a1] = i  31.234  3,201
 Inside: With Sheet1: .[a1] = i  33.375  2,996
 [a1].Value = i  34.375  2,909
 [a_named_range] = i  37.703  2,652
The clear winner is setting up an object variable to represent the range (it should be noted that “o” was dimensioned as a Range type variable), followed closely by the Cells keyword. If absolute speed is critical to your circumstance then one of these is the way to go.
But remember that these times were the cumulative total required to write a value to the range 100,000 times. So even the slowest method in the test was writing to the range at a speed of 2,652 writes per second!
An interesting note is that reading the ranges is six to seven time faster for each of the range methods, but their relative performance is roughly the same.
I want to also point out that sometimes we need to update the worksheet in a loop. For example to get the animation to show on a chart, we need to give the charting engine a chance to work. If we just run the loop without addressing this issue, we will never see the animation, just the completed state. There are two methods commonly used to do this. The first is to use:
Application.ScreenUpdating = True
inside the loop.
The second is to use:
DoEvents
inside the loop.
So I tested these on the fastest method of range referencing (the object variable method) in the same loop of 100,000 iterations. Here are the results:
Application.ScreenUpdating = True ---> 1,423.56 seconds
DoEvents --->; 65.33 seconds
No that is not a mistake on the first one. The ScreenUpdating method took nearly 24 minutes! So to update a worksheet in a loop, always use DoEvents.
One final point that you should be aware of is that the shortcut range reference only works from within Excel. If you are using some other language to automate Excel, the shortcut range reference will not work.
The shortcut range reference from VBA is a shortcut in the number of characters used to refer to a range, it is not a shortcut in code speed of execution. But for me, in many situations, the brevity and clarity are more important. I work hard to craft the examples I post to Excel Hero, making the VBA clear and concise. I do this to maximize their readability and teaching potential. I feel that my liberal use of the shortcut range reference supports that goal.
Enhanced by Zemanta

Excel Optical Illusions #15

This week I’m happy to showcase another fine Excel Optical Illusion put together by Hui. It’s called Counter-Intuitive Illusory Contours.

optical_illusions_illusory_contours_excelhero.com.gif
There are no lines connecting the smaller dots, but there sure seems to be, like a wave, or a mirage. I love it.
The animation is straight forward and the chart is just an XY (Scatter) type.
Great job again, Hui, and thanks for contributing to the community.
Here’s the chart.
Here’s a list of other animated charts on Excel Hero:
 – solarsong
 – Excel, A Presentation Platform (Number Spiral)
 – Lilac Chaser (Optical Illusion)
 – Stereokinetic (Optical Illusion)
 – Illusory Contours (Optical Illusion)
 – Breathing Square (Optical Illusion)
 – Enigma (Optical Illusion)

Animated Business Chart

Recently I’ve shared several Excel animated charts that have been well received. These have been largely entertainment focused applications. Today I will show you how to apply animation to normal business charts.

animated_business_chart.gif
This animated GIF shows the business chart in question which is available for download at the end of this post. Last week one of Excel Hero’s readers, Tom Quist, sent me a spreadsheet where he had made an outstanding first effort at the animation of a typical column chart. He wanted to animate between several records – let’s call them divisions.


His technique worked, but was a little slow. So I showed him how I would do it and I believe he was enthusiastic with the results.

There are some points to note. If you add a shadow to the chart or (God forbid) make the series 3D, the animation will slow down significantly. Ditto for a background image to either the Plot Area or the Chart Area. The animation speed is heavily dependent on the number of data points in the chart. After a few dozen points, animation becomes dicey in Excel 2007. Making the series partially transparent will also slow it down, but only slightly (maybe not even perceptibly given the number of points in the chart). In some circumstances the transparency effect may be worth the tiny penalty.


The technique that I shared with Tom that day was to do a straight linear interpolation between the point values of one division to the next. This works well. I shared with him that it was also possible to implement EASING so the animation has a soft start and end. I did not have time to give him an example, but I hinted at how to do it. The animated business chart that I’m sharing with you today includes the option for easing, and you can see it demonstrated in the GIF loop above.


The example also moves the calculations for the animation off of the sheet and into named formulas. To me this makes sense as there is no reason to clutter the sheet with the calculations required to morph one division into another.


The chart allows you to switch between four divisions, change chart type, enable animation, and enable easing. You can also set the number of discreet steps to include in the morphing transition between divisions, which will be useful if you run the workbook on older versions of Excel. The older charting engine is so much quicker than Excel 2007’s that you will want to increase the number of animation steps just to see it!


I’ve heard that Excel 2010 is supposed to improve charting speed. I’d love to hear how this performs on that platform.


This workbook demonstrates interactive dynamic charting, animated transitions including easing, and charting from named formulas. VBA is used only to loop through the number of animation steps that you specify, and to change the chart type.
An animated transition can add a certain amount of flare to your dashboard, but be careful not to overdo it. If used sparingly it can add a Flash-like level of interest, but remember that in and of itself, it does not add to the information the dashboard conveys.
I recommend studying the named formulas (CTRL-F3) and the VBA (ALT-F11) in the workbook. Nothing is protected and there are no hidden sheets. The knowledge gained can be used for much more than animation.
Here’s the animated business chart.
And here’s a list of other animated charts on Excel Hero:
 – solarsong
 – Excel, A Presentation Platform (Number Spiral)
 – Lilac Chaser (Optical Illusion)
 – Stereokinetic (Optical Illusion)
 – Illusory Contours (Optical Illusion)
 – Breathing Square (Optical Illusion)
 – Enigma (Optical Illusion)
 – Two Sinusoids (Optical Illusion)
 – Perpetual Collisions (Optical Illusion)
 – Freezing Rotation (Optical Illusion)
 – Reverse Spoke Illusion (Optical Illusion)
 – Stepping Feet Radial Illusion (Optical Illusion)
 – Swimming Fish (Optical Illusion)

– Mutually Interfering Shapes (Optical Illusion)

 – Kaleidoscope Motion (Optical Illusion)
 – Curry’s Paradox (Optical Illusion)
 – The Haze Illusion (Optical Illusion)
 – The Koffka Ring (Optical Illusion) (Bottom of post)
Enhanced by Zemanta

Interactive Advanced Excel Chart

To be honest, I have no idea what to call this kind of chart.

html5_full.png
I was inspired by this useful HTML5 readiness tool. As you move the mouse over the rays the index ray follows the mouse pointer so you know which category is which. It’s pretty cool.


Crafting this project I learned that there are some chart properties that you can access from the Excel 2007 Format Data Series dialog box that cannot be addressed from VBA; and vice versa.
For lines, you can set the Cap Type under Line Style in the regular interface. You have three choices: Square, Round, and Flat. Under Joint Type, you also have three choices: Round, Bevel, and Miter. Nowhere in the VBA object model can these properties be set. I needed to set the index ray to a Cap Type of Flat in VBA and it cannot be done – so it’s round. You would think that you could set it beforehand from the interface – and of course you can – but changing the line color from VBA also changes the Cap Type!
While hunting through the expansive forest of the VBA object model for 2007 charts, I found a couple of interesting objects that are not addressable from dialog boxes – and so 99.99% of Excel 2007 users are likely unaware of them. For chart lines (and some other items) you can set them to glow. You can set the color and the radius of the glow. You can also specify SoftEdges, and I used this on the background darkish ring in this chart. Again, these two interesting settings can only be addressed from VBA.
I don’t have Excel 2010 yet. I hope this kind of strange oversight is rectified in the new version.
This chart uses a modified version of Jon Peltier’s chart events class, and just like my Eurovision 2009 chart, the interactivity and animation only works when the chart is selected. When you first open the workbook the chart will be selected. But if you click on the worksheet instead of the chart, you will need to click the chart again to reengage the interactivity. Be sure to change the years by clicking on them at the top.
Here’s the chart.
Enhanced by Zemanta

Excel Optical Illusions #14

This week’s Excel optical illusion is called, Lilac Chaser. It is generously brought to us by one of Excel Hero’s Australian readers, Hui.
pinkdots.gif
If you stare at the black cross in the center and keep your focus there a very curious illusion transpires. At first you’ll notice that where a pink dot disappears a green dot appears. If you keep staring at the black cross, all of the pink dots disappear!
I think Hui did an outstanding job. This is a Radar chart with a little VBA thrown in to animate the dots, and it is well worth looking under the hood to see how it’s done.
Michael Bach has a similar illusion in Flash and it is of course outstanding, but ours is in Excel, dammit.
Great job, Hui and thank you for contributing to Excel Hero’s growing community!
The workbook is an .xls so it should work in all versions of Excel, but the newer versions render the gradients better and the illusion is stronger as a result.
Here’s the chart.
Reblog this post [with Zemanta]

Excel Animated Chart #2

In my humble opinion this is one of the most interesting advanced Excel charting projects I’ve ever done. I call it celtic muse, as it stirs my Scottish soul.
Click this image for a Flash Movie recorded from celtic muse.
 
If you enjoyed solarsong, I think you’ll be fascinated by this. It uses the Windows DirectShow API to play a haunting celtic strain. The show is very much enhanced by the soundtrack so please be sure to download the workbook!


My inspiration for celtic muse  was the “1,000 Blocks” sculpture by Curtis Steiner of the Seattle Art Museum. Using 1,000 identical walnut blocks he has created an almost unbelievable number of truly inspired tiled images. Each image uses all 1,000 blocks arranged in a grid 20 tall by 50 wide. Each block looks like this:
 
I thought it would be interesting to recreate his Flash presentation using Excel 2007. I’m calling this type of chart an Excel Tile Chart as each element of the image is one of the six faces of the above block. This technique could be very useful for displaying mathematical tessellations.
The advanced chart is quite a mashup of technologies. It stitches together a Flash movie (for the spinning sample block), the DirectShow API for playing the MP3, some other Windows APIs for extracting the embedded MP3 OLEObject Package from the workbook so it can be played, an advanced Javascript timer running in a web browser control to direct the timing of the slide show and to keep the music and the slides in sync after a pause/resume cycle, a fair portion of VBA to stitch it all together, and a demonstration of how to use the INDEX worksheet function to enable dynamic charting.
The chart itself is an XY (Scatter) type.
The presentation includes 78 images, which means 78,000 tiles had to be encoded to work with the chart. I’d like to thank my wife and my 10-year old daughter for helping with that task!
I’d also like to thank Robert Mundigl from Clearly and Simply. Robert was kind enough to review the celtic muse workbook prior to publishing and offered some excellent suggestions for improving it that I had not thought of. Thank you very much Robert.
Since this project makes heavy use of various Windows APIs I’m confident it will only work on Windows based computers. The workbook itself is 6.8 MB, almost all of which is the ebedded binary data for the beautiful MP3 soundtrack. I promise it’s well worth the download.
Here is the file.
— UPDATE —
It seems that the Flash integration in this project may not work for some, especially if you have a 64 bit version of Windows. Adobe is working on a version of Flash for 64 bits, but it is not ready yet. So, here is a version of celtic muse with all traces of Flash removed. Just in case you were wondering, Flash was only used to display that spinning cube that you can also see on this blog post (if you are running a 32 bit browser!). And that only happened whey you clicked on the question mark button. Here’s the Flashless version:
 
 
 
 
 
And here’s a list of other animated charts on Excel Hero:
 – solarsong
 – Excel, A Presentation Platform (Number Spiral)
 – Lilac Chaser (Optical Illusion)
 – Stereokinetic (Optical Illusion)
 – Illusory Contours (Optical Illusion)
 – Breathing Square (Optical Illusion)
 – Enigma (Optical Illusion)
 – Two Sinusoids (Optical Illusion)
 – Perpetual Collisions (Optical Illusion)
 – Freezing Rotation (Optical Illusion)
 – Reverse Spoke Illusion (Optical Illusion)
 – Stepping Feet Radial Illusion (Optical Illusion)
 – Swimming Fish (Optical Illusion)

– Mutually Interfering Shapes (Optical Illusion)

 – Kaleidoscope Motion (Optical Illusion)
 – Curry’s Paradox (Optical Illusion)
 – The Haze Illusion (Optical Illusion)

 – The Koffka Ring (Optical Illusion)
Enhanced by Zemanta

Excel Animated Chart

Many of us create Excel data visualizations everyday. But have you ever made an audiolization? Luke Twyman of whitevinyldesign.com has made a very intriguing creation in Flash.

Here’s my attempt at emulating his fine work in Excel:

I call my version solarsong. Click the image above to watch a Flash video of the Excel 2007 chart in action. Remember to turn your speakers on!


solarsong uses the orbital frequencies of the eight planets to spin a haunting melody.
This is an advanced Excel chart with a number of interesting points, including animation and MIDI sound. True to form the chart is rendered from named formulas and a tad of VBA is used primarily to advance the animation, but in this case it is also used to play the MIDI sounds.
You can adjust the tempo of the song, and you can pause it. When you click play after a pause, solarsong will choose a different MIDI instrument. The combination of different instruments and different speeds can result in a surprising number of varied songs, yet all retain the constant beat of our sun.
Just click on play.
Give it a spin and let me know what you think!
Here it is.
And here’s a list of other animated charts on Excel Hero:
 – solarsong
 – Excel, A Presentation Platform (Number Spiral)
 – Lilac Chaser (Optical Illusion)
 – Stereokinetic (Optical Illusion)
 – Illusory Contours (Optical Illusion)
 – Breathing Square (Optical Illusion)
 – Enigma (Optical Illusion)
 – Two Sinusoids (Optical Illusion)
 – Perpetual Collisions (Optical Illusion)
 – Freezing Rotation (Optical Illusion)
 – Reverse Spoke Illusion (Optical Illusion)
 – Stepping Feet Radial Illusion (Optical Illusion)
 – Swimming Fish (Optical Illusion)

– Mutually Interfering Shapes (Optical Illusion)

 – Kaleidoscope Motion (Optical Illusion)
 – Curry’s Paradox (Optical Illusion)
 – The Haze Illusion (Optical Illusion)

 – The Koffka Ring (Optical Illusion)
Enhanced by Zemanta

A Real-time Advanced Excel Chart

I’ve received quite a few PMs regarding the Multi-threaded VBA simulation. One person could not run the swarm due to a policy setting at work. I’d really love to know if anyone else has issues. It seems to be working for everyone else.

In case you are having difficulty I made these animated GIFs from screen recordings of the program running each of the three methods on my computer. All three produce the same data scraping results. The swarm is just way faster. And remember these results are from scraping a terribly slow website. Faster websites produce much more stunning results.
combined.gif
In case you haven’t downloaded the Multi-threaded VBA simulation workbook yet, an explanation is in order. The gray bar represents the number of records needed to be scraped. The blue bar represents the records that have been requested. The green area represents records returned. The red line represents the speed or rate in terms of records per second returned, which is what the vertical axis scale shows.
I’d like to know if anyone gets over 2.00 records per second from the incredibly slow subject website.
You can read the full article here.
And download the latest workbook here.

Multi-threaded VBA Update

As I’m sure you are aware, the visualization of the swarm in the original example workbook was just for show. But it’s possible that a new viewer of the program might think that it locked up when the program was waiting for agent payloads to be returned.

So I made a new version that keeps the swarm gratuitously swarming for no other reason than to make it look like they are busy doing something. I’ve never liked any of the timer options for VBA so I often use Javascript for this, like I have in this updated example. I’ve tested the workbook in Excel 2002 and 2007 and it seems to work ok. Let me know if you have any problems.
Here’s the update with the fully active swarm!
P.S. A minute ago I just about fell out of my chair. I ran the program and the swarm looked like they were on steroids, or maybe they were killer bees! It took me a second to realize that I had the Stereokinetic illusion running at the same time in the same instance of Excel but not in the foreground. Both programs make extensive use of calculation events and the two running at the same time was almost scary. I thought the swarm was about to take their revenge on me…

Multi-threaded VBA

VBA is a very capable programming language. With it we can craft extraordinary projects in Excel. However, it can only do one thing at a time. We can let it breath a little and jump between events with the DoEvents command, but I’m pretty sure that VBA will always be a single threaded affair, if for no other reason than complexity. Debugging multi-threaded programs in languages that truly suport threads, like C++, Java and C# is very difficult.

threads.png
A few months ago I penned a guest article for Ajay over at Databison, demonstrating a technique I’ve used many times to simulate multi-threaded VBA. Today I thought I’d share the technique with my readers and include an enhanced example workbook.


I should make it clear that this technique is not multi-threaded VBA, as such does not exist. Instead it is a method of orchestrating multiple processes from Excel, leveraging the fact that Windows is a multitasking operating system.
The scenario is scraping a website, an ideal candidate for this demonstration because it can be an asynchronous task. If you follow a traditional approach you might automate Internet Explorer from VBA. This is probably the worst choice because Internet Explorer is big, fat, and slow, but even worse, Excel’s VBA processor will sit waiting synchronously for the information from Internet Explorer, virtually killing the Excel user interface while the program is running.
A better technique is to forgo automating Internet Explorer and to use the MSXML2.XMLHTTP OLE object from Microsoft. While not strictly correct, you can think of it as a web browser with no interface. In other words, it’s invisible. It’s stripped of everything and just quickly retrieves web page text to memory. It can also send data to a server and is optimized for XML. This technique is much faster than the first. But it still ties up Excel synchronously as it scrapes one record at a time.
My preferred approach is to create and launch a swarm of independent agents from VBA. These agents do not run in Excel. They are not VBA. They are in fact VBScript programs that run in the Windows Scripting Host. I think of them as worker bees, because they are expendable. Each has one mission: go to the website of interest; get one record; write it back to the master Excel file that created it; die. A shotgun blast would be another analogy. After the payload has been received by Excel, a new agent is created and dispatched to replace the one that just terminated. This is simpler to manage than trying to reuse an existing agent.
The swarm method works very well and the speed increase is phenomenal, but dependent on a number of factors. The website being scraped is a big factor. In this demo, we’ll be scraping www.realestateabc.com, which is really slow. Several years ago before Zillow.com went to a Flash interface, this technique was unbelievably fast there. The operating system is another factor. I can run several dozen agents in a swarm on Windows XP. The same computer can run hundreds of agents in a swarm under Vista. Windows 7 is likely better than Vista, but I don’t have it and so I don’t know. But the more agents you can run simultaneously, the better the speed increase. I can only assume that Vista has a better OLE subsystem than XP. And of course the speed of your machine and the amount of memory it has will also affect the size of the swarm and its performance.
The example workbook showcases all three of these techniques allowing you to test the speed of each. I’ve also included an interesting chart to monitor progress and speed. It’s best to only run one instance of Excel during the testing. The first time you set the swarm loose, you may need to authorize each VBScript file, depending on your security settings.
Many of my examples on this site do not use VBA, or they use minimal VBA. This example is heavy on VBA, but interestingly most of the work is done by the VBScript agents when using the swarm. Of course when using the other two techniques, VBA is the workhorse.
For an added bit of enlightenment, run the swarm with the Windows Task Manager open to Processes, and the Image Name column sorted Z-A. You’ll see all the agent programs as they are spawned, and then near the end of the run, you’ll see them disappear.
This workbook is an example of fairly advanced Excel. The idea of Excel launching a swarm of programs that run in a different language and ultimately each write back to the Excel workbook that created them is… out there! Even the chart inclusion that monitors the progress and speed is a sophisticated specimen. I think that most users could learn a tremendous amount by fully studying this example workbook.
Please let me know what you think. If you have any questions on the methodology or the VBA or VBScript coding, please fire away.
Now imagine you had to scrape a hundred thousand records…
Here’s the file:
— UPDATE —
There’s an interesting enhancement to this sample workbook here.
Enhanced by Zemanta