Questions tagged [vba]

Visual Basic for Applications (VBA) is an event-driven programming language 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 and over 200 non-Office hosts.

Filter by
Sorted by
Tagged with
44
votes
3answers
11k 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>. ...
34
votes
4answers
8k views

A lightning-fast StringBuilder

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 ...
17
votes
3answers
2k 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 ...
27
votes
5answers
13k 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 ...
10
votes
4answers
2k 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 ...
24
votes
5answers
3k 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
1answer
2k views

VBA Class to persist and restore Excel Application properties

Very often on Stack Overflow, and even on Code Review, I've seen questions and answers that have code that begins by persisting Excel properties like DisplayAlerts ...
23
votes
1answer
3k 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 ...
6
votes
1answer
242 views

Comb Sort in VBA

Playing off Robust Bubble Sort in VBA and as suggested by @Henrik, I took a look at comb sort and tried to create an algorithm based on the documentation solely on Wikipedia. Basically, the first ...
15
votes
2answers
3k 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
2answers
640 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 ...
10
votes
3answers
632 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
2answers
119 views

Parsing cells containing Line Feed Characters

Link to sanitized xls on dropbox if test data is needed Essentially the reports I work with aren't bad - The issue is the way it exports to excel - With the problem being that these cells are ...
9
votes
4answers
727 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: ...
6
votes
1answer
390 views

Non-standardised data across multiple worksheets: Aggregation, Validation, Filtering

Every piece of business that gets written (E.G. an adviser's client puts an additional £10k payment into a pension plan) goes in a spreadsheet called the SubSheet. It's split into 8 worksheets for ...
6
votes
1answer
302 views

Looking up ingredients and creating a shopping list

This is my first attempt at refactoring - FindIngredients and I'm wondering where I can improve. The github link in the code directs to the project that still has ...
15
votes
5answers
108k views

Try catch statement in VBA using the standard VBA error handling statements

The code below is pretty self explanatory: just copy and paste it all into a module and run it, it provides a few use cases and many explanatory comments in the text. (It works but I'm interested to ...
37
votes
2answers
4k 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 ...
10
votes
1answer
4k 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 ...
12
votes
2answers
477 views

Finding the cheapest hotel

I thought this question made a good excuse for some basic OOP code, so I whipped up a little bit of code to demonstrate it in VBA; the idea is to have tutorial-grade code, to show how interfaces and ...
7
votes
0answers
838 views

Responding to Events of Dynamically added Controls

I was asked for an example of a class to wrap dynamically added controls as I suggested in this Answer. Although it is out of the context of a Code Review, I thought that it would make an interesting ...
10
votes
1answer
208 views

BattleShip Grid: Classes and Tests

In my ongoing quest to demonstrate how VBA code can absolutely be object-oriented, I've started implementing a game of Battleship in pure VBA. This is a rather large project, so I'll split the ...
6
votes
0answers
340 views

IndexedArray Class: Uses a Dictionary Keys to Index a 2 Dimensional Array of Values

This class encapsulates a 2D Array and a Scripting Dictionary. Values are add or returned from to the Array using a Key and a ColumnIndex, e.g. ...
15
votes
2answers
1k 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 ...
9
votes
2answers
290 views

Generating fiscal calendar dates

I need to generate a T-SQL script for loading data into this table: ...
8
votes
2answers
236 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 ...
7
votes
2answers
7k 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 ...
7
votes
1answer
145 views

A global Enum for comparison operators

This approach was born out of wanting to pass a logical expression (E.G. ">=10000") as a parameter to a function. So, I built an Enum and a function to use it to evaluate logical expressions. Is ...
5
votes
2answers
164 views

Very Simple Spell (Number to Words) 001 to 999 in VBA

I have come across many spell number functions and have reviewed many VBA code available on the net. The core function that gets called repeatedly is the function that converts numbers from 001 to ...
2
votes
1answer
59 views

Making a report from payroll details part II

Thanks for the help so far! Especially all of the people who helped out on part I - seen here. Making a report from payroll details Here's the revisions to the code based on feedback from that thread....
8
votes
3answers
315 views

If ComparisonIsTrue(thisPost, ComparisonOperator.NotEqualTo, goodCode) Then Me.Answer

This is a follow-on to a global enum for comparison operators. I wanted to pass a logical expression E.G. >= someValue as a parameter to a function, so I made an ...
7
votes
2answers
298 views

Making a report from payroll details

...
6
votes
1answer
599 views

A more readable InStr: StringContains

Consider the following: If myString = "abc" Or myString = "def" [...] Or myString = "xyz" Then In C# when myString == "abc" ...
6
votes
0answers
278 views

Battleship MVC Architecture

The entry point is the Macros module, which - for now - includes only a single procedure, at a very high abstraction level - I'm quite happy with this: ...
3
votes
2answers
349 views

Retrieve, total and display ingredients of meal

Here is the first iteration of this project on CodeReview Note: The input meal is data verified - it cannot be something that doesn't exist on the lookup sheet. ...
3
votes
0answers
83 views

Comb Sort Multi-dimensional Array on Key

Previously: Comb Sort in VBA I've been running two different comb sorts (on arrays) depending on whether I want to sort numbers or strings and I figured I would give using ...
2
votes
1answer
171 views

VBA syntax that compiles sub data into master sheet

I am using this syntax to bring in the names of all employees whom are active, then from there filter the data on subsequent worksheets and use a formula to bring the data over to the ...
1
vote
2answers
236 views

Retrieve data from eBird API and create multi-level hierarchy of locations

As you may know, I like birds. The Cornell Lab of Ornithology has a tool called eBird where you can submit records of the birds you identified. I recently learned they have an API that I can query, ...
1
vote
1answer
96 views

Looping through with varied subtotal ranges

I've got an issue where my looping through the rows on my sheet are extremely slow. For 20,000 rows, it takes about 20 minutes, whereas before, it was taking 3. The only pieces I added were at the ...
27
votes
1answer
3k 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 ...
20
votes
3answers
4k views

A reusable ProgressIndicator

There was a Tweet from @ExcelEasy earlier this week, that linked to this article, to which I replied with a little challenge: @ExcelEasy well done! #Challenge: make a more #OOP one without using ...
14
votes
3answers
4k 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 ...
12
votes
3answers
4k 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 ...
10
votes
1answer
28k 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 ...
11
votes
1answer
690 views

Standard Methods in VBA [closed]

Since I learnt about refactoring, I've been busy creating my own Module of Standard Methods. I feel it's about time they got a review of their own. Open to all aspects of advice but particularly ...
8
votes
1answer
575 views

Space Invader Style Game Written In VBA

EDIT: Link to the github, https://github.com/Evanml2030/Excel-SpaceInvader THE README (APPLIES HERE TOO): VBA version of a classic game Yes, I know that "Missile" is repeatedly written as "Missle"...
15
votes
3answers
2k 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 ...
15
votes
2answers
2k views

Using events together with interfaces in VBA

Introduction Because of the limitation of VBA in using events in interfaces I was searching for a kind of workaround. For sure I also read this which also provides an approach, but I was searching ...
14
votes
2answers
4k 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 ...
14
votes
2answers
993 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 ...