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

3
votes
1answer
41 views

Traversing and printing complex Dictionary types (Scripting.Dictionary) - Dictionary inside of a Dictionary

Based on this SO post - also reposted on vba4all.com with a few more details and explanations. Please notice there currently is no error handling whatsoever as I didn't analyse and consider any traps ...
-1
votes
0answers
14 views

Formatting in Excel and exporting to Word using VBA [on hold]

I am trying to export an excel table into a new word document. The excel data I am trying to export is dynamic inputs that each user can select and VBA will export as string values. I have three ...
3
votes
1answer
68 views

Searching a Word Document from Excel - v2.0

My first question: Searching a Word Document from Excel Since then I have implemented most of the suggestions by @RubberDuck and @Comintern. I have also added functionality for different-sized ...
8
votes
3answers
78 views

Automagic testing framework for VBA

Building on @RubberDuck's recommendations, I now have something I find... beautiful. I'm sure there's a couple of things left to polish - this site is about making great code out of good any code, ...
9
votes
3answers
94 views

VBA Python-like List Class

VBA's Collection class is lacking so I created a basic List class using Python's as a template. This could make future derived ...
6
votes
1answer
57 views

A hacked-up testing framework

Inspired by this post, I wanted to be able, in any vba project I could be working on, to create a test class and write test methods. Like this: ThisWorkbook Workbook | class module (client code) ...
9
votes
1answer
76 views

Unit Testing in VBA

Unit testing in VBA is... lacking. (What isn't lacking in VBA though?) Since I've become more interested in unit testing lately, I decided I needed something better than ...
9
votes
1answer
56 views

Yet Another Fraction

A recent question inspired me to implement a Fraction class. I decided to write this one in vba, because I like writing tools for the poor souls that still have to ...
8
votes
2answers
90 views

Searching a Word Document from Excel

Today I created a script in Excel that migrates data from a specific Word document and copies a portion of it to a cell in Excel (the date to be specific). The file input is Last name, First ...
6
votes
3answers
36 views

Progress Bar Form

Forms in MS Access are really just special glorified classes, so you can create new instances of them from code like this. ...
10
votes
2answers
67 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
65 views

UniqueList Class in VBA

I often find myself abusing dictionary objects just for the exist method like this, ...
6
votes
1answer
44 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
120 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
68 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
52 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
54 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
97 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
148 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
57 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
103 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
64 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
162 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
282 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
56 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
84 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
89 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
63 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
79 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
43 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
101 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
94 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
49 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
62 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
202 views

VBA Function slower than Excel Formula

I've replaced the following horrendous Excel formula (values and names have been replaced): ...
5
votes
2answers
77 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
98 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
88 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
91 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
58 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
497 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
302 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
162 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
102 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
155 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
82 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
60 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
104 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 ...