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.
1,312
questions
51
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>. ...
- 74.9k
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 ...
- 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 ...
- 74.9k
39
votes
2
answers
12k
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 ...
- 2,315
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 ...
- 74.9k
31
votes
1
answer
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 ...
- 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 ...
- 74.9k
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, ...
- 74.9k
28
votes
2
answers
938
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 ...
- 8,800
28
votes
0
answers
857
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 ...
- 381
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
...
- 7,149
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, ...
- 1,134
22
votes
1
answer
27k
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 ...
- 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 ...
- 74.9k
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 ...
- 74.9k
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 ...
- 25.2k
19
votes
2
answers
390
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 ...
- 311
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 ...
- 74.9k
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 ...
- 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 ...
18
votes
0
answers
623
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 (...
- 1,361
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 ...
- 171
16
votes
5
answers
131k
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 ...
- 323
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 ...
- 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 ...
- 74.9k
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 ...
- 253
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 ...
user28366
15
votes
1
answer
178
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 ...
- 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 ...
- 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 ...
user28366
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 ...
- 143
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 ...
- 74.9k
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 ...
- 74.9k
14
votes
3
answers
837
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 ...
- 141
14
votes
2
answers
2k
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 ...
- 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 ...
- 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 ...
- 8,800
14
votes
1
answer
491
views
Extensible logging - DatabaseLogger
Recently I wrote a logging API that features an ILogger interface. I wanted to extend my library with a DatabaseLogger ...
- 74.9k
14
votes
1
answer
980
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 ...
- 2,315
14
votes
0
answers
523
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 <...
- 74.9k
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 ...
- 20.3k
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 ...
- 231
13
votes
5
answers
867
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, ...
- 2,315
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 ...
- 1,134
13
votes
2
answers
760
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 ...
- 74.9k
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 ...
- 74.9k
13
votes
1
answer
885
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 ...
- 295
13
votes
1
answer
607
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 ...
- 2,315
12
votes
4
answers
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 ...
- 223
12
votes
3
answers
770
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 :
...
- 359