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

9
votes
2answers
114 views

StringBuilder in VBA

In the process of trying to build a serializable data structure, I found myself building large strings, which gets very slow because VBA copies a string every time concatenation is performed. To ...
-1
votes
0answers
29 views

Insert variable number of rows [on hold]

In the Excel vba sub below, I need to insert the number of rows equal to variable j after row k. So if J=17 and k=2, then I ...
7
votes
1answer
39 views

Joining collections… because it's fun

I'm working on a LinqEnumerable class (reviewable here), and I'm no Big-O expert and I'm pretty bad with algorithms, so I wonder what the complexity is for what ...
17
votes
2answers
185 views

Wait, is this… LINQ?

Context I'm working on a little project that consists in a series of Microsoft Excel add-ins (.xlam). The code being submitted for review here, is located in the ...
6
votes
1answer
43 views

Passing a 1D array (single column of a Range object) to .NET to manipulate it without loops and returning it back to VBA

Background Kind of a follow up - slightly related to my other SO question. I thought that if somehow I find a way in VBA to pass a single column to .NET and convert it to a native .NET type then I ...
8
votes
2answers
82 views

Generating and calling code on the fly

Delegate This class module defines what I'm calling, in this context, a Delegate - here a function that can take a number of parameters, evaluate a result, and ...
5
votes
2answers
53 views

Checking for missing values in several columns of a spreadsheet

Basically I had 3 columns of Data Adjudicated Cases Not Adjudicated Cases All Cases and I wanted to get a list of all the cases that were missing from Column A and Column B, with a little help ...
6
votes
4answers
69 views

Extracting key information from a directory of XML files

I have written some VBA to loop through a directory of 15,000 XML files and extract key information from these files into Excel - this is unbearably slow. Can someone please assist me in optimising ...
10
votes
2answers
157 views

LogManager Tests

I should have started with this code. I wrote my logging API without writing unit tests, and since I recently wrote an automagic unit testing API I though I might as well go ahead and use it. So I ...
11
votes
1answer
91 views

Extensible logging - DatabaseLogger

Recently I wrote a logging API that features an ILogger interface. I wanted to extend my library with a DatabaseLogger ...
13
votes
1answer
89 views

Extensible logging

Whenever I need logging functionality in .net, I use a logging framework, such as NLog. Obviously there's no logging framework for vba, at least none that I know of. As much as I love using NLog, the ...
2
votes
2answers
25 views

Enforcing Type with Boiler Plates

This is an implementation of Mat's List using by Pseudo-inheriting my Pythonic-List. Inheritance is not built into VBA, but it can be simulated by composing the inherited class and mimicking all it's ...
4
votes
1answer
38 views

Glob-Like Search in VBA

The Dir() function in VB and VBA can accept wildcard characters (* and ?) but only in the basename of the path. ...
6
votes
2answers
81 views

Updating Pivot Tables in Excel [closed]

I'm writing some code for someone to update a pivot table field based on the contents entered in a particular cell. I am unfamiliar with VBA and programming for excel but I followed some tutorials ...
9
votes
3answers
120 views

Ugly workaround to get the vbext_ProcKind of a procedure is breaking encapsulation

This is a follow up to Extending the VBAExtensibility Library. It turns out that code had a really nasty bug. Anytime vbeProcedure.StartLine got called, I was ...
8
votes
2answers
116 views

Unit Testing - A Better Solution

Following-up on the Automagic testing framework for VBA review, I've refactored much of the TestEngine static class, and introduced a ...
4
votes
2answers
78 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 ...
3
votes
1answer
86 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 ...
11
votes
5answers
135 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, ...
10
votes
3answers
119 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
65 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) ...
10
votes
1answer
108 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
65 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
93 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
56 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
68 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
73 views

UniqueList Class in VBA

I often find myself abusing dictionary objects just for the exist method like this, ...
6
votes
1answer
48 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
46 views

Decoupling Presenter from “child” Repository

Still pursuing the white rabbit, I had an IPresenter interface implementation featuring this method: ...
10
votes
3answers
178 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
75 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
62 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
70 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 ...
10
votes
2answers
116 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 ...
9
votes
3answers
164 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
59 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
116 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
75 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
552 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
283 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
64 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
88 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
98 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 ...
3
votes
2answers
110 views
8
votes
1answer
65 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
81 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
75 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
108 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
103 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
86 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 ...