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>. ...
36
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 ...
35
votes
2answers
5k 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 ...
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 ...
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 ...
27
votes
1answer
2k 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 ...
26
votes
2answers
812 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 ...
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, ...
24
votes
3answers
806 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 ...
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 ...
22
votes
4answers
1k 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, ...
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 ...
20
votes
1answer
15k 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 ...
19
votes
2answers
375 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 ...
19
votes
1answer
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 ...
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 ...
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 ...
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 ...
15
votes
3answers
1k 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 ...
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
1answer
157 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 ...
15
votes
2answers
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 ...
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 ...
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
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 ...
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 ...
14
votes
2answers
989 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 ...
14
votes
2answers
1k 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 ...
14
votes
3answers
618 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 ...
14
votes
2answers
2k 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 ...
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
1answer
1k 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 ...
13
votes
6answers
297k 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 ...
13
votes
5answers
2k 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 ...
13
votes
1answer
313 views

Extensible logging - DatabaseLogger

Recently I wrote a logging API that features an ILogger interface. I wanted to extend my library with a DatabaseLogger ...
13
votes
1answer
875 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 ...
12
votes
4answers
1k 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 ...
12
votes
3answers
567 views

Speeding up execution time of worksheet-formatting code

I've been writing some code that places some data in an empty worksheet and then formats the sheet by adding borders to all of the used cells. The code functions how it should perfect, but it takes a ...
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 ...
12
votes
2answers
491 views

Suspiciously redundant Excel macro code

I need a little help in finding a better solution. In the screen shot below any change to the forecast line I need to find the value in column B. The screen shot is only a sample in my production ...
12
votes
2answers
318 views

Calculation of an inflation on volume/year

I would like to find out if my current solution to the problem described below is "good enough" or if there is an alternative way of achieving it. All I care about is the length (no. of lines) of the ...
12
votes
2answers
461 views

Forecast maintenance interval for fleet of aircraft

Being new to VBA, and many years removed from college programming courses in Java, I'm seeking opinions about design, implementation, readability and general better practices using VBA to tackle ...
12
votes
2answers
119 views

Follow-up: Randomizing Civilization 5 team choice

This is a follow-up on my previous question about a VBA macro for randomizing a draw from a table. It can be found here: Randomizing Civilization 5 team choice The code has been improved with the ...
12
votes
1answer
5k views

Creating ShortCut (RightClick) Menus in Access

I'm writing a tool to create shortcut menus for an application that I maintain built on the Access runtime. I compile this app into an *.accde file, so much to my chagrin, all right click menus are ...
12
votes
1answer
308 views

Shared VB6 *and* VBA extensibility add-in with OnConnect and OnDisconnect handling

Rubberduck works well enough when the VBE is of the VBA variety, but it currently only works when the Rubberduck is configured to load at VBA start-up, and it has a tendency to crash the host ...
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 ...
12
votes
0answers
205 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 ...
11
votes
4answers
714 views

Copying values from one sheet to another

I am trying to copy values from one sheet to another using loop with two conditions and it is very slow. Removing conditions from code doesn't have any effect on the speed of the execution. I already ...
11
votes
4answers
520 views

Get Workbook Method(s)

I'm re-writing my Module of standard methods. These 3 are used to retrieve Workbooks as Workbook Objects. The standard I'm aiming for here is "Third-party Library/Add-in". So: Do these functions ...
11
votes
3answers
475 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 : ...