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

5
votes
3answers
76 views

Building an OPC client in Excel

I am pretty deep into building a very multi-faceted little app in Excel with VBA. It does a number of tasks, all centered around using OPC to get tag values from several PLC's and doing various ...
7
votes
1answer
219 views

Improving performance of a VBA code to read contents of many .CSV files

I'm fairly new to VBA and just completed my first script. It's completing the task I want it to do just fine, but it's really slow* in doing so. It has to open and read about 1000 csv-files, for each ...
6
votes
2answers
33 views

Cascading Changes to Future Entries in a Schedule

I've been working on a scheduling application and I have the middle tier completed at this point. It's not changed in a few days, so I feel it's ready for review. I have just this one routine that ...
6
votes
1answer
40 views

Run arbitrary INSERT INTO SQL Query using parameters

While DoCmd.RunSQL is all well and good for simple code, the number of times I've run into problems with unescaped apostrophes and the like was starting to nark a ...
5
votes
1answer
41 views

Conditionally copying cells from other worksheets

I wrote super redundant VBA code and it works and solve my problem. I am trying to simplify it but don't know where to start due to my limited knowledge in VBA. Basically, this code works as a ...
7
votes
1answer
70 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
57 views

Generating fiscal calendar dates

I need to generate a T-SQL script for loading data into this table: ...
5
votes
1answer
54 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 ...
12
votes
3answers
117 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 ...
5
votes
2answers
86 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
79 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
138 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
91 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 ...
7
votes
2answers
90 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
125 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
106 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
254 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
107 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 ...
19
votes
2answers
213 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
140 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
89 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
110 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
122 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
169 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
95 views

Extensible logging - DatabaseLogger

Recently I wrote a logging API that features an ILogger interface. I wanted to extend my library with a DatabaseLogger ...
14
votes
1answer
105 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
29 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
49 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
125 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
142 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
140 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
117 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
115 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
183 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
160 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
68 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
152 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
75 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
117 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
98 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
87 views

UniqueList Class in VBA

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

Decoupling Presenter from “child” Repository

Still pursuing the white rabbit, I had an IPresenter interface implementation featuring this method: ...
10
votes
3answers
255 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
83 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
72 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
97 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
148 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 ...