VBA Error – “code execution has been interrupted”

Yet another bug! I’ve been quite heavily into VBA lately, and that’s one reason for such error posts! This has been quite a pain in the neck for VBA programmers, esp. if you haven’t quite hit upon this before.

This is how the story goes…

You’re heavily into the coding of your product, and suddenly, you figure out that you’ve entered an infinite loop. What do your instincts say?? Ofcourse! Ctrl+Break! Now, that was eass, wasn’t it… But what follows isn’t!

So, going forward, you actually go on making more tweaks in your code, and as you run it, you suddenly find an error (the title name). You try running it manually then; to your surprise, it runs quite fine. Again, when you run the whole code, the error pops up. To your frustration, this happens quite often within one piece of code! No Logical or Autorun Errors, and still… You finally turn to the one last resort – Google!

And the story ends thereafter ~

Here’s the reason why this happens. This is a so called poorly documented piece of information by Microsoft, but we users have taken care of that.

Once you press Crtl+Break, it trips off a mode that makes the compiler go to the break mode at random intervals. Nothing you do can stop this, unless you restart your machine. This is specially the case when you’ve got custom formulas, that run devoid of any programming.

Otherwise, you could easily put a fix to this in your program by using the following line at the start of your procedure:

Application.EnableCancelKey = xlDisabled

This helps reset the errorhanding at the end of the execution of your code. Which implies, you don’t need to worry about xlEnabled. But as I said, sometimes, it’s only a restart that can help you! (Don’t forget to save before you restart) Quite a pain in the ***! Especially if you are in the middle of something.

Info on EnableCancelKey

This property has three modes as listed below:

xlDisabled -> which ignores pressed keys and solves the problem
xlInterrupt -> which sets the error handling back to Excel’s control
xlErrorHandler -> wich makes error go to your error handling section

Thus when the code you are running is done, this property is set back to its default. Hence no need to worry about that.

A nice piece of information if ever you get caught in this.

 

 

Error 1004: Select method of Class Failed

This seems to be a popular ‘Google’ find! I didn’t know what to do when I was confronted with this error! And Google was there to my rescue…

Most of you must have hit upon this error, and by far, I saw more posts that had this question without a solution! Let me muster a few thoughts that I was able to gather from various places on the Internet!

Working with Event Handlers, most of you must have tried a code like the one below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim copyrng As Range, pasterng As Range
    Sheets2.Select
    Set copyrng = Sheets2.Range(“A1″)
    Set pasterng = Target.Value
    copyrng.Copy
    pasterng.PasteSpecial xlPasteValuesAndNumberFormats
End Sub

What is amiss here is that this code is an Event Handler, written in Sheet1’s Worksheet_SelectionChange. And we’re selecting Sheet2 from here! Isn’t there a problem that you see???

Working with Event Handlers, it’s best not to pick ranges from other sheets inside them directly. If there is a requirement to do so, it’s best to use Modules.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Call Module1.Test
End Sub

Sub Test()
    Dim copyrng As Range, pasterng As Range
    Sheets2.Select
    Set copyrng = Sheets2.Range(“A1″)
    Set pasterng = Target.Value
    copyrng.Copy
    pasterng.PasteSpecial xlPasteValuesAndNumberFormats
End Sub

I had really started to even doubt my own knowledge of the Range functions. Anything with the range will start to work in a funny way, and you’ll be totally thrown off. Hope it helps more people who fall into the trap of “Select method of Class Failed”

Also, this is probably just one occurance that I encountered! I encourage more people to come up with their instances so we know when all this can actually happen..

 

Using the RefEdit Control in Excel

I’m not sure how many are aware of the RefEdit Control in excel. Infact, all of us are, only no one knows it by this name!

How many times have you had to select a range from an Excel Sheet? Umpteen! And in all of those, you’d have found this tool that minimizes the window application inside excel, and lets you choose the range while also entering the range dynamically in the TextBox of

the Application used. This is the RefEdit Control and is quite useful, though not entirely!

Here to the right is how this looks like… Now you get it??!

So, now what are the big plusses of this Control?

Actually, I know of just one, and it’s a huge one!

The thing is, RefEdit lets you select a set of cells/ranges/multiple ranges without without actually activating the selection. Hence, the input window is still active while we select the range of cells, and the selection is highlighted by the marching ants boundary! Hence, the Excel.Application.Selection does not change when RefEdit is used! Largely the one thing every programmer would love to have!

But it doesn’t come without a bag of pitfalls! And it’s quite a big bag!

1. It can only be used with VBA Userform – Not COM Add-in, not anywhere! (You have Microsoft’s word on this!)

2. When used from a Excel 2007 and saved, it creates issues when re-opening from other Excel prior to 2007, or from other locations if the file is publicly accessible/shared. (Solution to this lies in just re-adding the RefEdit reference to the same path eg. C:\Programs
files\Microsoft Office\office11\RefEdit.dll, then it should be solved usually!)

3. The RefEdit contains 2 modes – Enter mode & Edit mode. This is both a boon & a bane. Boon because you can resort to editing the range without having to re-select the range, specially if it’s elsewhere than the current activesheet. Bane, because, handling automated tasks without being manually able to control Enter/Edit separately would be a disaster!

There are some alternatives that I got when I googled. Though, none works as best as this one, along with offseting the negatives. Guess you can’t have the apple AND eat it!

One popular alternative is this -Excel.Application.InputBox Type:=8. Here the type:=8 signifies a range selection input. But this is not the best way, since one, it selects the range thereby changing the selection and two, it next to impossible to have multiple range selection or other worksheets/workbooks! (I’m currently trying to work around this. Suggestions will be greatly appreciated)

There’s one more tool that I found – http://www.oraxcel.com/downloads/index.htm?product=litrefedit. I haven’t checked this out, but I did see elsewhere that it was a good alternative, but a very nascent one. Needs to evolve more before it can replace RefEdit.

So that said, check out if you want to try out using this anywhere on your forms. And please do come back with comments if you have any on next steps on this. I’ve also quite read some issues trying to be able to handle the data after the selection is done to input it elsewhere. I’ll keep that for future, although comments here, again, are welcome.

 

Array Functions Part II – 3D Arrays in Excel

Array functions were one of my first forays into Excel. But I couldn’t do enough justice to them in the beginning, and hence felt the need for another post.

What led to this post? I was looking for a bit on Arrays, when I bumped into sites I hadn’t referred to before. And believe me, there is more to Arrays than even what I know now! But I thought some parts of it I must bring out. It all started when I was looking to get the list of unique items, and that is when I bumped into the formula I described in my prev. post. The Power Formula one. It was quite heavy in Arrays, so I decided to probe a little further into how it works, and then the ball rolled over from one topic to another, and finally landed here – 3D arrays in Excel!

Arrays in Excel Sheet space

If you want to enter an array, all you have to do is enter this: ={0,1,2,3,4,5}. And if you “Array-enter”(Ctrl+Shift+Enter) it, you find the 6 cells (in columns) populated but the array. So basically, this is it to enter an array.

Named Array Ranges : This is a little different from the usual named ranges we have in Excel. Usually, a name defines/refers to a specific cell contents. So it’s actually a reference to the cell location, and not a formula! If we need to name the array we entered above, and we do the usual naming on the top left corner cell, I think you’re going to be in for some surprise when you use the name.

Essentially, named array ranges are the same as “Array Variables”. So, when you go to the Name Manager, you need to specify the name as shown in the screenshot. This way, when you use the name, it substitutes the Array in its place.

This is such a handy option, and if you get the hang of Arrays, I think you’d be in love it it. If you’re used to matrices processing or stuff, like Matlab, I think you’ll find this good.

To know more on what all an Array in Excel can support, look up this link (http://www.emailoffice.com/excel/arrays-bobumlas.html) which gives every possible way of ‘how to use arrays’. The Calendar in there is too damned good a formula, that I trying to make a sweet calendar for a 25 year period with features like what Outlook offers, w/o the Mail options.

Setting out on this calendar stuff is when I hit upon the topic of 3D Arrays.

3D Arrays in Excel: In Excel sheet space, 3D array refers to cell ranges across multiple sheets, for eg. ({=Sheet1:3!A$1:A$3}) is considered a 3D array formula, since it involves 3 Dimension – Rows, Columns & Worksheets. The following link has some information on this (http://www.bettersolutions.com/excel/EED883/QN620422111.htm). But nowhere was I able to find a 3D array similar to the lines of using it in VBA i.e. X(1:2:3). In VBA, just a Dim X(2,2,2) as Variant makes it an array to hold a 3×3x3 matrix. I haven’t found enough documentation whether this is possible in Excel Sheet space.

Coming back the the power formula, it sure does suit its name, for there is so much “array munching n crunching” that happens in the formulas, it has definitely expanded my comfort zone as far as Arrays are concerned.

 

Filtering Unique Cells/ Unique Values/Distinct Values or Removing Duplicates in Excel

“Filtering out the distinct values of a Column/Variable/Range” is one of the most sought after functions in Analytical Reporting. Yet, it fails to figure upfront in the “Home” section of Excel’s Ribbon! Is it something that you wouldn’t need often in Excel? If no, how easy is it to use the currently available options in Excel? Are there alternatives? Please read further…

Primarily, the need for this function is for multiple key merges. In other tools, you generally have in-built mechanisms to consider only the distinct values of the primary key, and merge two datasets with multiple keys. Excel hardly has anything of this mechanism, and to be more precise, merging in excel is limited to “single-key merges”. Merge not being the topic in question, I’ll let that pass this time.

The “Advanced Filter” (from the Data Menu) is the in-built mechanism in Excel to filter out the unique cell values of a variable/column/range. Remember to check the “Unique Records only” check box.  Excel 2007 has a “Remove Duplicates” key, which is a definitely useful, but as far as 2003 is concerned, it’s still short of user friendliness.

Using this, you have two options,

• Filter in-place: This option leads to filtering of the range in the current location. Loss of data is inevitable, unless you have a copy of it
• Copy to another location: This is the more useful option, which helps transport your list of unique values to the desired location

Enter the location of the range to be filtered in “List Range”, and the location of destination in “Copy To”. The “Criteria Range” is used to filter out any desired/undesired criteria, in other words, filtering out the condition required. Filtering using a Criteria has many variants to it, and I am not going to delve into it. Nevertheless, here’s a reference for the same. This link (http://www.contextures.com/xladvfilter01.html) provide exhaustive information on Advanced Filter, and also on Criteria Range variants.

If there is a need to filter the distinct items quite a lot of times, I bet anyone would get frustrated following the above process and willl proceed to look for alternatives. That is what happened with me too, and I hit a couple of gold mines! Let me put down the various methods that can be adopted to do this filtering I’ve been talking about. Honestly, it still intrigues me to find a new method everytime!

1. Using Arrays - This method still evades me. The following link (http://spreadsheetpage.com/index.php/eee/issue_no_4_april_30_1999/) explains this method in the “Power Formula Technique”. It basically consists of three arrays,  Thelist: This consists of the expanding list of values to be filtered; sArray: This array consists of the locations of the unique values; and finally, TheUniqueList: This array consists of the sorted list of values. I’m not very sure how we can convert this array back into the normal Excel ranges. As I said, the array concepts are making it a little tricky, so I’m working on it to get to know it’s full potential. Here’s the part of it I am talking about.
2. Using Range.RemoveDuplicates & Range.SpecialCells(xlCellTypeBlanks).Delete in VBA: Using this is really quick and simple, though customizing this to your needs may require some attention.
3. Using the Collections Object in VBA: This method is the fastest method as far as comparison in terms of speed goes. Refer to this page for a readymade piece of code, that runs like a darling! (http://www.dailydoseofexcel.com/archives/2008/12/11/create-unique-list-from-selected-cells/). Again, I’m not very versatile with Collections, so I can’t explain much. But it’s pretty slick. The page also has various other techniques listed, some of which I’ll jot down below.
4. Using the Dictionary Object in VBA: Dictionary is very similar to Collections in being able to store & handle related data as groups. The advantage of Dictionary over Collections being the use of “KEY” property. For more comprehensive understanding, I would recommend visiting Pearson’s site (http://www.cpearson.com/excel/CollectionsAndDictionaries.htm).
5. Using the 1D Array Options (split, join, filter): This is a very innovative way of executing the filter, and this was something very new to me too. Look it up in the link provided with the Collections Method.

And believe me, there are other unexplored methods too. Phew! That’s some exhaustive work!

This is only interesting as it gets deeper, but from the surface, I would believe a small code on the Collections or the RemoveDuplicates should be more than helpful & quick. So, if you could make yourself an Add-In, it might be useful too; only if you’re frequently going to use something like this.