Search Results

In any case, your approach will be slowed down by the frequent context switches between the VBA runtime and the Excel function evaluation engine. …

A faster alternative to writing the values individually is to assign the Variant array to the Value property of the top left cell of the range you want to past it into. That writes the array in one go …

The most probable culprit for your performance issues are the constant context switches betwwe vba and Excel whenever you call find. …

To make the code more readable and more flexible, i.e. more maintainable, you might try to apply some general principles and some useful features of VBA/Excel. … You can define named ranges in Excel and then get the ranges by name in VBA. …

Then you could avoid querying Excel for the last row each and every time you copy rows. …

Using Excel Functions If you do not want to use VBA at all, you can modify the previous approach to use a suitable Excel workbook function. …

Let me start with a number of general guidelines that can make your code more maintainable and more reusable. After that I will give some more specific advise regarding your code. General Guidelines …

(See this blog post testing the various ways to copy the values in ranges in Excel.) …

Performance Next, let me give some performance tips that do not change your code radically: Accessing a worksheet in Excel VBA is expensive. … An alterntive approach would be not to use Excel at all and instead use a real database, e.g. Access for starters. …

You only ask about performance, but I want to point out anyway that there is a small error in the definition of file2RC: you used column instead of row. Now, let me get to the performance part. The …

The outermost could simply do the enabling and disabling of Excel features and call a private procedure doing the comparison in between. …

Reading in both the payment sheet and invoice sheet this way, allows you to iterate over both without switching back to Excel for every value. … In VBA the largest Integer is 32767, but a sheet in modern Excel can have millions of rows. You should really use Long instead. …

After having a short look at your code, I get the impression that your database accesses in a double loop are the cause of the long execution time. First of all, it is generally much faster to pull a …

Data Input After the general pieces of advice applicable to basically all programming languages, let me come to some more Excel and VBA specific point. … Tables Even better would be to turn the input range into an Excel table, which we will call SourceTable. …

This looks like a decent recursive implementation. So, this is mainly about style and readability. First, I would like to talk about two things which are really hard and could use some improvement he …