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
46
votes
3answers
13k 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>. ...
37
votes
4answers
10k 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 ...
28
votes
5answers
14k 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 ...
11
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 ...
18
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 ...
25
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, ...
26
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 ...
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 ...
6
votes
1answer
265 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
5answers
118k 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 ...
13
votes
2answers
586 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 ...
17
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
717 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
686 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 ...
9
votes
4answers
841 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: ...
5
votes
2answers
121 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 ...
6
votes
1answer
407 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
365 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 ...
4
votes
3answers
190 views

Brute force looping & formatting Or Create Union range & Format? Which is efficient and when?

May be my question is not up to the standard for Code Review, but of upmost importance for reassessing methods used in my VBA coding . While preparing/testing answer for a SO post (thought to simplest ...
2
votes
1answer
38 views

Part 2: Send HTTP request for each row in Excel table

This script was previously reviewed here: Send HTTP request for each row in Excel table (Part 1) I've made the changes that were suggested in the Code Review answers as well as added a few of my own. ...
39
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 ...
7
votes
0answers
2k 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 ...
11
votes
2answers
518 views

Solution for Parent/Child circular references: WeakReference class

Sometimes in advanced OOP scenarios, a class needs to hold instances of another class which needs to hold a reference to the "parent". For example when you have a dynamic ...
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 ...
10
votes
1answer
249 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
471 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. ...
12
votes
3answers
577 views

Perf wrapper for Excel VBA

Spoiler This code is actually BAD for performances! So don't use it! ;) For a while now, I've been using this wrapper to avoid retyping most of it : ...
9
votes
2answers
243 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 ...
9
votes
2answers
294 views

Generating fiscal calendar dates

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

Making a report from payroll details

...
7
votes
1answer
674 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
411 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
0answers
82 views

Looking for more efficient VBA to Move data from Teradata to Excel

I have a simple macro moving data from Teradata to Excel. The process normally takes me 10 minutes to perform manually, however the macro can run for hours sometimes. This is making the macro seems ...
3
votes
2answers
352 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
1answer
190 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 ...
3
votes
0answers
90 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 ...
1
vote
1answer
123 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 ...
1
vote
2answers
310 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, ...
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 the ...
28
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 ...
15
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
5k 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 for ...
10
votes
1answer
29k 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 ...
8
votes
1answer
878 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"...

1
2 3 4 5