Visual Basic for Applications (VBA) is an event-driven programming language which was first introduced by Microsoft in 1993 to give Excel 5.0 a more robust object-oriented language for writing macros and automating the use of Excel. It is now used for the entire Office suite. If your question is ...

learn more… | top users | synonyms

8
votes
2answers
48 views

Disposable Heroes

I had a bit of an issue with my last piece of code, having to do with cleaning up resources. I needed a way to ensure the database connection was always properly closed, even if there still were ...
5
votes
2answers
50 views

UniqueList Class in VBA

I often find myself abusing dictionary objects just for the exist method like this, ...
6
votes
1answer
41 views

UnitOfWork, an excuse for multiple repositories

I needed a way of attaching multiple repositories to a presenter. Given I already had an IRepository abstraction, coming up with an ...
5
votes
1answer
44 views

Decoupling Presenter from “child” Repository

Still pursuing the white rabbit, I had an IPresenter interface implementation featuring this method: ...
8
votes
2answers
95 views

Building a better Collection. Enumerable in VBA

VBA's 'Collection' is.... lacking, so, I've been working on a better Collection object that implements many of the features of C#'s Enumerable. This is very much inspired by this question and a follow ...
4
votes
2answers
61 views

Finding matching strings in a column

I have col D (in sheet1 called Students), col A (in Sheet2 called Students too) and col B ...
4
votes
3answers
48 views

Inefficient (slow) loop with calculations in worksheet

I am looking for best practice help. Slow loops seem to be a recurring problem for me and I'd like to learn a better way. The code itself works as it should, except it is far too slow. The problem ...
4
votes
2answers
46 views

Higher Order Functions in VBA, the dirty way

I have been building a small library of VBA functions. I have a small section with higher order functions that I'd like to share. It uses strings as delegates/function pointers and ...
8
votes
2answers
93 views

Sorting a collection

Related to, but not exactly a follow up of this question. After fixing some issues discovered in the last review, I added a little more functionality to the ...
8
votes
3answers
133 views

Speeding up execution time of worksheet-formatting code

I've been writing some code that places some data in an empty worksheet and then formats the sheet by adding borders to all of the used cells. The code functions how it should perfect, but it takes a ...
3
votes
1answer
54 views

Optimize spreadsheet-filling routine

I'm stuck with the slow VBA routine below, which I think is the cause of lagging (taking to long). I was wondering if there is a faster way to do this: ...
3
votes
3answers
91 views

A practical example of evenly distributing n lists into a single list

I had previously asked about how to evenly distribute the items in n lists into a single list and was referred to this question. I made a practical example of my solution for this in VBA for Excel, ...
8
votes
3answers
61 views

More imitation of Enumerable in VBA

I was inspired by Me How's question to see how far I could push an imitation of .Net's Enumerable Class. The new functions can obviously handle Collections, but can also handle any collection-type ...
5
votes
3answers
98 views

Assigning value to cells with Excel VBA

I have an Excel Worksheet consisting of two columns, one of which is filled with strings and the other of which is empty. I would like to use VBA to assign the value of the cells in the empty column ...
8
votes
2answers
280 views

Optimize total code in one sub

I have the following code which works fine, but there has to be a faster way to do this. I want to have it all in one sub. ...
4
votes
1answer
53 views

Increasingly Long Runtime for Macro

My code works, but the problem is that it is taking an increasingly long time to run, with the time required to complete calculations increasing every time I use the macro. I've tried a variety of ...
6
votes
2answers
79 views

Basic Imitation of C# Enumerable in VBA - or any other static class

Since implementing static classes in VBA actually seems possible I came up with an idea to try to imitate C#' Enumerable class (well, to some extent...). This is ...
8
votes
2answers
83 views

No more Fizzbuzz. How about Hello World instead?

Write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of ...
2
votes
2answers
102 views
8
votes
1answer
62 views

Honey I shrunk the view

Going down the rabbit hole, I wanted to be able to change the size of a form, and not have to worry about the layout of the controls. vba doesn't support user-resizeable forms, but forms still have a ...
7
votes
2answers
78 views

Down the rabbit hole with MVP

Following-up on this post where I implemented a Repository Pattern in vba to abstract ADODB and enable testing my Excel app without hitting a database backend; curious about how far vba would let me ...
10
votes
1answer
34 views

Creating ShortCut (RightClick) Menus in Access

I'm writing a tool to create shortcut menus for an application that I maintain built on the Access runtime. I compile this app into an *.accde file, so much to my chagrin, all right click menus are ...
10
votes
2answers
98 views

YARPI: Yet Another Repository Pattern Implementation

Following-up on this post, I wanted to be able to put a copy of that Excel workbook on a USB key and take it home to keep working on the code a bit (there's more than just one or two tables to ...
10
votes
1answer
83 views

Abusing Excel VBA… to maintain data stored in a database

I need to build a little UI to allow easy maintenance of some data stored in a MySQL database, and the end user wants to do that in Excel (I know, I know). The code I came up with comes pretty close ...
6
votes
1answer
46 views

Possible memory leak in a for loop macro

I was reading about similar problems to the one I am having, and my guess is that I am having a 'memory leak'. I'm not sure exactly what that means, or how to correct it. Could you take a look at my ...
8
votes
2answers
61 views

How can I make this userform timer faster?

I have a userform that displays a goal time for workers to shoot for when completing a task. It also has a stopwatch on it that is controlled by a start, stop, and reset button on the userform. If the ...
5
votes
2answers
193 views

VBA Function slower than Excel Formula

I've replaced the following horrendous Excel formula (values and names have been replaced): ...
5
votes
2answers
74 views

DRYing up an initialization pattern

I maintain a VBA process that prints a large number of PDFs. It was awful when I inherited it and I've been making improvements over time, but I'm a bit stuck on this one. There are at least 4 ...
7
votes
2answers
83 views

Script for saving/sorting/sending emails based on their subject

I've spent a couple days making this script in Outlook that checks my emails and saves/sorts/sends a reply based on the subject. This also happens to be my first experience in working with VBA. Now ...
4
votes
1answer
80 views

Ignore exceptions when changing column formula in Excel table

The new table feature on Excel is excellent and you can specify a column formula as well as exceptions on the column. However, if you want to change the column formula, Excel will rewrite the whole ...
5
votes
1answer
80 views

Word macro is very slow, looking for improvements

I am using the following macro to insert a mini table of contents between two identical heading styles (e.g. Heading 1). It works fine on small documents, but is very slow on large documents. Any ...
5
votes
1answer
56 views

Test if Excel is open without relying on catching an error

Getting a hook on an open instance of Excel is something I do all the time. I'm now questioning if I'm doing this the best way. I'm particularly concerned with the fact that I rely on the ...
3
votes
1answer
350 views

MS Access VBA code to compare records in a table and combine data - revised

I've previously posted a version of this code here: MS Access VBA code to compare records in a table and combine data where necessary To repeat what I'm looking for from my previous post: Note I ...
4
votes
2answers
214 views

MS Access VBA code to compare records in a table and combine data where necessary

I need to process some data which is the output from a CAD system, namely a Bill of Materials. I've constructed database and written some VBA code to achieve this. An explanation of what this code ...
7
votes
1answer
143 views

Extending the VBA Extensibility Library

The Microsoft Visual Basic for Applications Extensibility library let's us meta-program VBA, but the way it handles (or rather, doesn't handle) getting to the actual subs and functions is clunky at ...
7
votes
1answer
92 views

File reader/writer (text)

Following-up on this post, I wanted to be able to use my FileWriter with a syntax reminiscent of .net's using blocks, which ...
7
votes
3answers
142 views

“Scripting.FileSystemObject” Replacement module in VBA

I have found Scripting.FileSystemObject to be slow and unstable. Calling file_object.Name repeatedly has caused my code to crash excel on multiple instances. ...
7
votes
3answers
78 views

FileWriter supporting writing to multiple files

I got bored with opening and closing files whenever I need to write to one, so I wrote a FileWriter class that's used like this: ...
4
votes
1answer
59 views

Recursively searching the Windows Registry

This function is part of a the Registry class that can be found on Google Drive. The class was originally written by Steve McMahon for VB6, but I ported it to VBA ...
5
votes
2answers
102 views

Domain Name Service Class

I recently wrote a DSN class for use with the Access flavor of VBA. I'm preparing to refactor and would appreciate feedback. I am aware of two issues. I added the ...
5
votes
3answers
91 views

Improving performance of VBA batch scripts?

I am using a script to open a bunch of Excel files, copying two or more sheets into a new file and saving this new file. It sure beats doing it manually, but I think it could be faster. Here's my ...
6
votes
3answers
123 views

Retrieving data from files in the folder

I am to use VBA code to simply retrieve certain data from all Excel files in certain folder and paste it in working spreadsheet. I am not sure that this is the most efficient (which matters as I would ...
8
votes
2answers
185 views

Calculation of an inflation on volume/year

Background I would like to find out if my current solution to the problem described below is "good enough" or if there is an alternative way of achieving it. All I care about is the length (no. of ...
6
votes
1answer
104 views

Traffic light implementation

Can someone review this code for me? I'm not completely sure I did this correctly. I also would like help creating the code for the "walk" "don't walk" lights. I needed to create a traffic ...
8
votes
2answers
68 views

In place quicksort

I implemented my own sorting in VBA because it doesn't provide its own, and it's healthy to test yourself. It was surprisingly difficult and I ended up make a few tweaks that I didn't expect to make ...
6
votes
2answers
101 views

Materializing any ADODB Query

Following-up on Creating ADODB Parameters on the fly and pushing the "wrapping" of ADODB a step further, I have written two more classes that allows me to expose methods that don't require a ...
7
votes
2answers
475 views

Creating ADODB Parameters on the fly

I have put together a small wrapper class to simplify creating parameterized ADODB queries with VB6/VBA. At this point I'm keeping things simple, so it's only supporting input parameters and from what ...
3
votes
1answer
86 views

Making VBA generated dynamic SQL statements safe against injection

I need to use SQL extensively in VBA macros that I write. Since the DB is from our enterprise application, I use vendor's library for this. I generate dynamic queries, sometimes with many ...
4
votes
1answer
41 views

Ranking a variant array with variable dimensions

I'm doing VBA macros and need to use Arrays of values extensively. After the below help from @TonyDallimore in StackOverflow, I've decided to use nested variant arrays. VBA chrashes when trying to ...
3
votes
1answer
88 views

KeyValuePair implementation

In order to implement my own Dictionary<TKey, TValue> in VB6, I've implemented a KeyValuePair class, which can accomodate ...