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

4
votes
0answers
12 views

Access function to return “number of working days until date” for form

I need a function I can put on a form which does: Calculates number of working days until a given date Excludes weekends Excludes any dates in a "holiday" table This is replacing something which ...
6
votes
2answers
40 views

Generating fiscal calendar dates

I need to generate a T-SQL script for loading data into this table: ...
5
votes
1answer
46 views

VBA Code Profiling

I have wanted a way to do code profiling in VBA for quite some time. It becomes very complicated to figure out what methods are actually being executed for how long and how often in complex Access ...
10
votes
3answers
99 views

Creating an object oriented model in VBA using COM and ADODB from 2 depended SQL tables

The story... A bit of background info and how is the database designed... Please notice you don't really have to rebuild the tables in SQL but I shared an SQL Fiddle just in case and screenshots1 of ...
4
votes
2answers
56 views

VBA ClickBot featuring AJAX waiting and Element searching

If you work with a database that you only have access through a web interface, this one is for you. You probably don't have access to any programming languages other than VBA through MS Office, which ...
6
votes
2answers
68 views

Immutable Linked List in VBA

I made an immutable list class using the head-tail idiom. If I did this correctly, it implements persistent data structures. Unfortunately it doesn't scale well ...
6
votes
1answer
101 views

Excel VBA binary search class module [closed]

I have a long list of sorted strings in a Workbook, and I have to find a lot of values in it. I was hoping to build a binary search Class Module that will find them for me faster than Excel's built-in ...
6
votes
1answer
64 views

Overriding all Access form control AfterUpdate methods

I have a set of custom event handlers for all my textboxes and combo boxes. The reason for this is my users want the bound form (to SQL Server) to immediately push changes to the recordset, rather ...
6
votes
2answers
79 views

Enter the Matrix

I've semi-often been frustrated at the lack of a proper Matrix data structure in VBA. A multi-dimensional array is obviously the right way to handle it, but there is so much missing... for example, ...
5
votes
3answers
113 views

Parameterised Constructors

Finally, Parameterised Constructors are possible in VBA. Background: One rainy day, I came up with an idea: take a static class and expose one property as default (like it was done in the Reverse ...
4
votes
2answers
89 views

VBA Excel Game - Testing

My brother bet me that I couldn't make a game in Excel. The code is all over the place and buggy in parts. But I would appreciate it if anyone would give this game a go. The music and the code has ...
11
votes
2answers
171 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 ...
3
votes
1answer
90 views

Insert variable number of rows

In the Excel vba sub below, I need to insert the number of rows equal to variable j after row k. So if ...
7
votes
1answer
42 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
206 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 ...
9
votes
1answer
104 views
+50

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
88 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
76 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
87 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
165 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
94 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
102 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
28 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
42 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
98 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
135 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
131 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
97 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
104 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 ...
12
votes
5answers
164 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
141 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
136 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
72 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
100 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
76 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
69 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
79 views

UniqueList Class in VBA

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

Decoupling Presenter from “child” Repository

Still pursuing the white rabbit, I had an IPresenter interface implementation featuring this method: ...
10
votes
3answers
219 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
76 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
68 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
91 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
127 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
173 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
60 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
126 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
81 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
2k 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 ...