Hot answers tagged

15 votes

Multi-dimensional array sort and filter functions

Stop. Close Excel. Take a deep breath. Now go and get an actual Database. MS Access. MySQL is free and Open-Source. It doesn't really matter. What matters is that Excel is not the program to be ...
Kaz's user avatar
  • 8,790
15 votes
Accepted

Copying values from one sheet to another

Lowest Hanging VBA Fruit: ...
Kaz's user avatar
  • 8,790
15 votes

Macro that does some kind of duplicate check

Lowest-Hanging VBA Performance Fruit You have With Application .ScreenUpdating = False .EnableEvents = False .DisplayAlerts = False End With But you only have ...
Kaz's user avatar
  • 8,790
15 votes
Accepted

A new approach to multithreading in Excel

Interesting idea and well-done! Naming I really don't like the names. The names like clsMultiThread is somewhat misleading, since as you noted they don't actually ...
this's user avatar
  • 2,019
14 votes
Accepted

Data entry of Information

You include Option Explicit :+1 for that alone. Range without any explicit worksheet implicitly is working off of whatever ...
IvenBach's user avatar
  • 3,477
13 votes
Accepted

Module for implementation in multiple version-controlled spreadsheets

Just things that jump out at me: call is redundant. SubName arg, arg, arg5:=arg var = FunctionName(arg, arg, arg5:=arg) Is ...
Kaz's user avatar
  • 8,790
13 votes
Accepted

Write to EXCEL from SQL DB using VBA script

Range.CopyFromRecordset only addresses the [massive] performance issue of traversing an entire recordset row by agonizing row and writing it to a worksheet cell by ...
Mathieu Guindon's user avatar
12 votes
Accepted

Copy, Paste And Format

The 3 lowest-hanging VBA performance fruit are: Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlManual ...
Kaz's user avatar
  • 8,790
12 votes

Small datasheet code running super slow on Excel VBA

You do not have look at more than about 2 Code Review answers to find posts that advocate two very important principles when writing code (in VBA or any other language). The "Do not repeat ...
BZngr's user avatar
  • 1,217
11 votes

A lightning-fast StringBuilder

Using CopyMemory is actually unnecessary, you can achieve the same thing simply with arrays. The code is not only shorter but faster as well. ...
Martin.Roller's user avatar
11 votes
Accepted

Extract values within date and zone ranges

Boy is there a lot to talk about here. First things first, if Option Explicit is not at the top of every code module, go to tools --> Options --> Require Variable ...
Kaz's user avatar
  • 8,790
11 votes
Accepted

Macro code that removes excess rows

Here's that conditional, reformatted with line continuations for readability: ...
Mathieu Guindon's user avatar
11 votes
Accepted

Loop through a unique combinations driven by choices

1) The best performance gains often come from not hitting the sheet unless you really need to. One way to do this is to read a whole range into an array, then access that instead of reading cell-by-...
Tim Williams's user avatar
11 votes

Converting from .xlsx to .txt

Your code is incredibly fragile directory = ThisWorkbook.Sheets("Macro").Range("D576").Value rdate = ThisWorkbook.Sheets("Macro").Range("E47").Value If a single ...
Kaz's user avatar
  • 8,790
11 votes
Accepted

Delete rows in spreadsheet where cells match some patterns

You know what really speeds up vba? ARRAYS! Why do stuff on the sheet when you can do it in an array? ...
Raystafarian's user avatar
  • 7,129
11 votes
Accepted

Evaluating formulas greater than 255 characters

The function is implicitly public, takes an implicitly ByRef parameter that has no reason to not be passed ByVal, and returns an ...
Mathieu Guindon's user avatar
10 votes

Get Workbook Method(s)

I will not mention again that I find the outdenting of Dim statements hurt readability. Instead I'll focus on this part: ...
Mathieu Guindon's user avatar
10 votes
Accepted

VBA Class to persist and restore Excel Application properties

I like the idea very much - encapsulating this concern in its own class is very OOP and certainly helps writing cleaner VBA. I like that you're wrapping all fields in a private type, leaving only <...
Mathieu Guindon's user avatar
10 votes
Accepted

Replace many Outlook email rules with a script

Here's some changes I would make. First thing I would do is make most of this module level variables, initialized in your Application_startup method, and taking advantage of WithEvents to setup the ...
enderland's user avatar
  • 1,726
10 votes

Conditionally deleting rows in a worksheet

You have fallen prey to a common temptation: writing a script. The problem with a script, is that it feels clunky and wrong, a bit like macro-recorder code does. What is this code achieving? ...
Mathieu Guindon's user avatar
10 votes
Accepted

Extracting Country Names from Cell Values

The small(er) things before we tackle the elephant in the room: Naming Your names aren't terrible, but could be a lot better. However, whatever you name things, you should comply with standard ...
Kaz's user avatar
  • 8,790
10 votes
Accepted

Perf wrapper for Excel VBA

First, I'd like to echo @Mat'sMug's distaste for your multi-lined Dim statements. There's absolutely no reason to do this at all having them prefixed with ...
Comintern's user avatar
  • 4,192
10 votes
Accepted

Code which pulls from SQL and dumps in Excel

First of all let me make an important comment not related to performance: Security issues I hope your table is only accessible by by a limited number of trusted people. I say this because building a ...
M.Doerner's user avatar
  • 1,490
9 votes

List<T> implementation for VB6/VBA

Your implementation of Sort would be optimised by implementing IndexOfMin so that ...
Mark Hurd's user avatar
  • 326
9 votes
Accepted

Life is a Game - Play it

You always need to know where you want to go. Actions shouldn't be split up to much. If you are going to set different functions for different actions (which is something good), they should to be "...
Dirk Reichel's user avatar
9 votes
Accepted

Deleting rows from a spreadsheet where Column C is blank

Things I like: Going last row to first, so as to avoid messiness with row numbers that occur when deleting whole rows. Good use of With Good function naming of <...
Kaz's user avatar
  • 8,790
9 votes
Accepted

Handling name variants

First, I thought I'd mention that your code is stylistically really good - variable and function names make sense, indentation is great (with one small exception noted below), and the functions are ...
Comintern's user avatar
  • 4,192
9 votes

Copying values from one sheet to another

TL;DR - Minimise calls to Range.Value... work on VBA arrays... Turning off screen updates etc will have an effect, but I'd suggest the biggest single thing slowing down your code is that you're ...
Tim's user avatar
  • 191
9 votes

Macro that does some kind of duplicate check

There are a variety of clean up things you can do first, because the code is confusing in what exactly you're trying to do. Always use Option Explicit. It will ...
PeterT's user avatar
  • 2,096

Only top scored, non community-wiki answers of a minimum length are eligible