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
52 votes
3 answers
15k 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>. ...
Mathieu Guindon's user avatar
43 votes
4 answers
13k 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 ...
Blackhawk's user avatar
  • 1,134
40 votes
2 answers
5k 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 ...
Mathieu Guindon's user avatar
39 votes
2 answers
13k views

A new approach to multithreading in Excel

Intro Multithreading tools exist in Excel - often to run macros in multiple instances of Excel, or to convert macros to vbscripts that can run independently. However I've often come across projects ...
Greedo's user avatar
  • 2,345
33 votes
5 answers
16k 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 ...
Mathieu Guindon's user avatar
31 votes
1 answer
4k 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 ...
RubberDuck's user avatar
  • 30.8k
29 votes
1 answer
5k 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 ...
Mathieu Guindon's user avatar
28 votes
5 answers
4k 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, ...
Mathieu Guindon's user avatar
28 votes
2 answers
952 views

Life is a Game - Play it

I haven't made a game in Excel yet, so I decided to Make Conway's Game of Life. If you wish to play along, I've uploaded the workbook here. Right now it's very rudimentary (No user interface, no ...
Kaz's user avatar
  • 8,800
27 votes
3 answers
1k views

Racetrack in... VBA?

So, I took a look at the August Challenge: The top-voted answer is Racetrack1: "In the game of Racetrack2, cars race around a track bounded by two concentric closed loops drawn on a ...
Raystafarian's user avatar
  • 7,149
27 votes
0 answers
1k views

Pacman implemented in OOP VBA

What?, Why? I have been inspired by several other posts on the topic of OOP implementations in VBA to try and create a Pacman clone. I think this task is not all that hard in most languages; but, I ...
ArcherBird's user avatar
24 votes
4 answers
2k 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, ...
Blackhawk's user avatar
  • 1,134
22 votes
1 answer
28k views

Snake in Excel in... VBA?

snake-game is a game where you control a snake in an enclosed square without running into the walls or your own body. The snake grows when you run into whatever item you are supposed to run into. My ...
Raystafarian's user avatar
  • 7,149
21 votes
3 answers
5k 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 ...
Mathieu Guindon's user avatar
20 votes
3 answers
3k 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 ...
Mathieu Guindon's user avatar
20 votes
1 answer
5k 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 ...
Vogel612's user avatar
  • 25.2k
19 votes
2 answers
394 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 ...
Simon Karfunkel's user avatar
19 votes
2 answers
4k 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 ...
Mathieu Guindon's user avatar
18 votes
3 answers
6k 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 ...
enderland's user avatar
  • 1,736
18 votes
2 answers
3k 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 for ...
AHeyne's user avatar
  • 428
18 votes
0 answers
1k views

Progress Bar for Excel

Any future updates can be found at Excel-VBA-ProgressBar What I've been using so far For the last 6 years I've been using a progress form which I wrote in VBA using just Windows API calls. The code (...
Cristian Buse's user avatar
17 votes
7 answers
320k 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 ...
user50726's user avatar
  • 171
16 votes
5 answers
132k 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 ...
HarveyFrench's user avatar
15 votes
3 answers
5k 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 ...
RubberDuck's user avatar
  • 30.8k
15 votes
2 answers
2k 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 ...
Mathieu Guindon's user avatar
15 votes
3 answers
2k views

How can I improve this 31-bit SuperFastHash implementation in VBA?

I'm implementing a variation of the SuperFastHash in VBA for use in Excel (32-bit version, so no LongLong available) to hash strings. To get around the limitations of signed 32-bit Long values, I'm ...
richardtallent's user avatar
15 votes
3 answers
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 ...
user avatar
15 votes
1 answer
183 views

Module for implementation in multiple version-controlled spreadsheets

So I maintain a large number of spreadsheets which are stored on a version-controlled server. In each spreadsheet maintained on this server there is a module containing the following code, which is ...
Toast's user avatar
  • 253
15 votes
2 answers
3k views

Win32 File API in VBA

Win32 File API Wrapper Based on a few fundamental frustrations with VBA (namely the lack of ability to work with files larger than 2GB, the lack of encapsulation of the file functions and the lack of ...
Blackhawk's user avatar
  • 1,134
15 votes
2 answers
2k 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 ...
user avatar
14 votes
2 answers
3k views

Avoiding repeated code in worksheet

Can someone help me make this code more professional? I'm trying my best to find something similar, but I wasn't successful. I want to avoid the repeat of the code for every single value and also I ...
user3016646's user avatar
14 votes
2 answers
2k 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 ...
Mathieu Guindon's user avatar
14 votes
4 answers
3k 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 ...
Mathieu Guindon's user avatar
14 votes
3 answers
849 views

Modularise Project and Deduplicate Code By Loading External Modules On Startup

Starting point I kind-of inherited a VBA project at work that consists of a bunch of Word document templates that provide users with the tools they need to create documents according to company ...
Chris's user avatar
  • 141
14 votes
2 answers
3k views

Regex Matching a Naming Convention

Program Purpose So, I have a naming convention for certain folders. I want to take in a folder name, and determine if it conforms to the convention. Naming Convention The convention (case ...
Kaz's user avatar
  • 8,800
14 votes
1 answer
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 ...
ThunderFrame's user avatar
  • 1,969
14 votes
1 answer
2k views

Template UserForm Input - launch, input, validation, error handling

I'm building a template [Process-Tracker] spreadsheet. The idea is that, for any process where we can define what steps should occur in what order (and preferably, how far apart), there will be a ...
Kaz's user avatar
  • 8,800
14 votes
1 answer
526 views

Extensible logging - DatabaseLogger

Recently I wrote a logging API that features an ILogger interface. I wanted to extend my library with a DatabaseLogger ...
Mathieu Guindon's user avatar
14 votes
1 answer
1k views

Tick. Tick. *breathe* BOOM! - Setting up real, stable asynchronous callbacks with WinAPI Timers in VBA

WinAPI Timers can be quite tricky to work with, as anyone who's tried to use them and fallen foul of one of the many pitfalls probably knows. Problems such as screen freezing, crashes, uncontrolled ...
Greedo's user avatar
  • 2,345
14 votes
0 answers
552 views

BattleShip - Ships & Players

I'm making a full-blown OOP Battleship game in VBA (previous post: Battleship Grid), and I want this code to be as good as it gets - and better, if I can. I've refactored a few things, introduced a <...
Mathieu Guindon's user avatar
13 votes
5 answers
3k views

Replace many Outlook email rules with a script

I as well as several of my colleagues have had an ongoing problem where we get bombarded with hundreds if not thousands of emails each day which come from automated services, such as SQL DBmail and ...
Phrancis's user avatar
  • 20.4k
13 votes
3 answers
2k views

Immutable Object class in VBA – Creatable only through constructor and not via “New” keyword

Goals for the class Create Immutable objects – i.e. only Getters – no Setters Object creation only possible through a constructor, not via New keyword, to ensure ...
Mark.R's user avatar
  • 231
13 votes
6 answers
921 views

Assign a variant to a variant

Quick review: I've been writing API code which receives user input that can come as either an object or a value type - a problem outlined here. In order to write an unknown data type to a variable, ...
Greedo's user avatar
  • 2,345
13 votes
1 answer
1k views

Functional Framework

First Class Functions So, VBA doesn't support functions as first class objects - no passing functions as arguments or storing them in variables. I eventually found a way to implement function ...
Blackhawk's user avatar
  • 1,134
13 votes
2 answers
798 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 ...
Mathieu Guindon's user avatar
13 votes
1 answer
1k 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 ...
Mathieu Guindon's user avatar
13 votes
1 answer
945 views

"String Interpolation in {language}", "VBA"

"This is {0} cool!", "freaking" I've always wanted an easy and intuitive way to inject variables into a string. So after about 10 variations, I finally came up with this function. How it works The ...
Robert Todar's user avatar
13 votes
1 answer
1k views

Low Level VBA Hacking - making Private functions Public

NEW: Download demo files Not sure what to title this - essentially, inspired by RubberDuck's unit test engine, I've created a way to call private methods of standard modules in VBA. It also lets you ...
Greedo's user avatar
  • 2,345
12 votes
4 answers
2k views

Delete rows in spreadsheet where cells match some patterns

I have vba code that loops through a large number of records and deletes rows based on criteria. The issue at hand is that it takes far too long to run. I have never actually let it finish because it ...
PartyHatPanda's user avatar
12 votes
3 answers
827 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 : ...
R3uK's user avatar
  • 359

1
2 3 4 5
27