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 ...
15
votes
2answers
2k views
List<T> implementation for VB6/VBA
Recently I decided VB6's Collection wasn't enough for my needs, so I decided to implement something like C#'s List<T>. ...
10
votes
3answers
2k 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 ...
8
votes
2answers
257 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 ...
6
votes
2answers
144 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 ...
12
votes
5answers
217 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, ...
5
votes
2answers
569 views
A CSharpish String.Format formatting helper
A while ago I implemented .net's string.Format() method in VB6; it works amazingly well, but I'm sure there has to be a way to make it more efficient.
I'll start ...
8
votes
3answers
130 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 ...
7
votes
3answers
123 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:
...
14
votes
1answer
114 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 ...
11
votes
2answers
221 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
1answer
545 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
5answers
468 views
Managing book of Excel sheets
This is a follow-on from a previous question I posted here.
I've got code here that works for what I want, but the problem is the loop takes ages to perform. I was wondering if anyone could follow ...
5
votes
2answers
117 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 ...
8
votes
2answers
100 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 ...
3
votes
1answer
173 views
A more readable InStr: StringContains
Consider the following:
If myString = "abc" Or myString = "def" [...] Or myString = "xyz" Then
In C# when myString == "abc" ...
2
votes
1answer
141 views
Calling Stored Procedures with Lots of Parameters
I have this piece of code for running a Stored Procedure, and I was wondering if there is a way of cutting code like:
...
12
votes
2answers
610 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 ...
12
votes
3answers
182 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 ...
10
votes
1answer
242 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 ...
10
votes
3answers
404 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 ...
5
votes
1answer
160 views
Macro for inserting a mini table between heading styles
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.
...
4
votes
2answers
2k views
Form controls - Single event handler for multiple controls, without sacrifices
Bit of background info first - I've been struggling with a problem for a while now. How could you have one single event handler to handle every control (or all of a particular type of control) on a ...
21
votes
2answers
350 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 ...
8
votes
2answers
109 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 ...
7
votes
1answer
177 views
Revisited IsTypeSafe method implementation for “type-safe” List
Following up on List<T> implementation for VB6/VBA, I'd like some thoughts about the revisited IsTypeSafe function, below.
The previous version pretty much ...
4
votes
2answers
905 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 ...
3
votes
1answer
1k 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 ...
14
votes
1answer
1k views
Importing data from an external EXCEL-Sheet
I was assigned the task of copying some subsums from a given EXCEL-Sheet into the executing EXCEL-Sheet.
This had to be done with an EXCEL-Macro, so non-programmers can easily use it. There was the ...
10
votes
3answers
252 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 ...
10
votes
2answers
138 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 ...
8
votes
2answers
157 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 ...
8
votes
2answers
471 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 ...
8
votes
2answers
91 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
1answer
72 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 ...
3
votes
1answer
155 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 ...
3
votes
2answers
119 views
14
votes
2answers
262 views
Randomizing Civilization 5 team choice
This macro is made to randomly draw specific number of civilization choices for a specific number of Civ V players. It is based on a list of 43 possibilities and the ...
10
votes
2answers
299 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 ...
10
votes
1answer
160 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 ...
7
votes
3answers
324 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
2answers
134 views
Generating a collection of controls
This question I asked previously mentions a function named BuildControlCollection, which I didn't go into the details of since it wasn't relevant. However, because ...
6
votes
1answer
77 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)
...
5
votes
1answer
148 views
Copy Cell on Worksheet_Change event if some criteria is met
This code is working fine, but it looks awful (yes, I am a rookie on this thing). How can I improve it to look better?
...
5
votes
1answer
57 views
Decoupling Presenter from “child” Repository
Still pursuing the white rabbit, I had an IPresenter interface implementation featuring this method:
...
4
votes
1answer
46 views
Average interval between dates with random blanks
I have a spreadsheet with order date data:
I need to find the average interval in days between each order date. I have to both find a way to get past the blank cells in the row, and also take into ...
4
votes
2answers
92 views
Retrieving stock prices
It takes around 5-8 seconds for me to retrieve a previously-closed stock price and a dividend rate from US Yahoo! Finance. If I wanted to retrieve 10+ stock prices, it would take me more than a minute ...
3
votes
1answer
264 views
KeyValuePair implementation
In order to implement my own Dictionary<TKey, TValue> in VB6, I've implemented a KeyValuePair class, which can accomodate ...
