Tagged Questions
Visual Basic for Applications (VBA) is an event-driven programming language which was 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. If your question is ...
0
votes
0answers
27 views
Questionnaire form in worksheet [on hold]
I'm trying to push out a questionnaire at work with several questions on. Users can reply with radio buttons ranging from yes, some, maybe and no. This questionnaire is contained in the sheet called ...
4
votes
2answers
26 views
Extracting key information from a directory of XML files
I have written some VBA to loop through a directory of 15,000 XML files and extract key information from these files into Excel - this is unbearably slow. Can someone please assist me in optimising ...
7
votes
1answer
102 views
+200
LogManager Tests
I should have started with this code. I wrote my logging API without writing unit tests, and since I recently wrote an automagic unit testing API I though I might as well go ahead and use it.
So I ...
9
votes
1answer
78 views
+100
Extensible logging - DatabaseLogger
Recently I wrote a logging API that features an ILogger interface. I wanted to extend my library with a DatabaseLogger ...
12
votes
1answer
82 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 ...
2
votes
2answers
23 views
Enforcing Type with Boiler Plates
This is an implementation of Mat's List using by Pseudo-inheriting my Pythonic-List. Inheritance is not built into VBA, but it can be simulated by composing the inherited class and mimicking all it's ...
4
votes
1answer
28 views
Glob-Like Search in VBA
The Dir() function in VB and VBA can accept wildcard characters (* and ?) but only in the basename of the path.
...
6
votes
2answers
66 views
Updating Pivot Tables in Excel [closed]
I'm writing some code for someone to update a pivot table field based on the contents entered in a particular cell.
I am unfamiliar with VBA and programming for excel but I followed some tutorials ...
7
votes
2answers
97 views
Unit Testing - A Better Solution
Following-up on the Automagic testing framework for VBA review, I've refactored much of the TestEngine static class, and introduced a ...
4
votes
2answers
75 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 ...
3
votes
1answer
77 views
Searching a Word Document from Excel - v2.0
My first question:
Searching a Word Document from Excel
Since then I have implemented most of the suggestions by @RubberDuck and @Comintern. I have also added functionality for different-sized ...
9
votes
5answers
116 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
3answers
103 views
VBA Python-like List Class
VBA's Collection class is lacking so I created a basic List class using Python's as a template. This could make future derived ...
6
votes
1answer
60 views
A hacked-up testing framework
Inspired by this post, I wanted to be able, in any vba project I could be working on, to create a test class and write test methods. Like this:
ThisWorkbook Workbook | class module (client code)
...
9
votes
1answer
88 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 ...
9
votes
1answer
61 views
Yet Another Fraction
A recent question inspired me to implement a Fraction class. I decided to write this one in vba, because I like writing tools for the poor souls that still have to ...
8
votes
2answers
93 views
Searching a Word Document from Excel
Today I created a script in Excel that migrates data from a specific Word document and copies a portion of it to a cell in Excel (the date to be specific).
The file input is
Last name, First ...
6
votes
3answers
49 views
Progress Bar Form
Forms in MS Access are really just special glorified classes, so you can create new instances of them from code like this.
...
10
votes
2answers
67 views
Disposable Heroes
I had a bit of an issue with my last piece of code, having to do with cleaning up resources. I needed a way to ensure the database connection was always properly closed, even if there still were ...
5
votes
2answers
67 views
UniqueList Class in VBA
I often find myself abusing dictionary objects just for the exist method like this,
...
6
votes
1answer
44 views
UnitOfWork, an excuse for multiple repositories
I needed a way of attaching multiple repositories to a presenter. Given I already had an IRepository abstraction, coming up with an ...
5
votes
1answer
46 views
Decoupling Presenter from “child” Repository
Still pursuing the white rabbit, I had an IPresenter interface implementation featuring this method:
...
8
votes
2answers
143 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 ...
4
votes
2answers
70 views
Finding matching strings in a column
I have col D (in sheet1 called Students), col A (in Sheet2 called Students too) and col B ...
4
votes
3answers
58 views
Inefficient (slow) loop with calculations in worksheet
I am looking for best practice help. Slow loops seem to be a recurring problem for me and I'd like to learn a better way. The code itself works as it should, except it is far too slow.
The problem ...
4
votes
2answers
60 views
Higher Order Functions in VBA, the dirty way
I have been building a small library of VBA functions. I have a small section with higher order functions that I'd like to share. It uses strings as delegates/function pointers and ...
9
votes
2answers
108 views
Sorting a collection
Related to, but not exactly a follow up of this question. After fixing some issues discovered in the last review, I added a little more functionality to the ...
9
votes
3answers
156 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 ...
3
votes
1answer
58 views
Optimize spreadsheet-filling routine
I'm stuck with the slow VBA routine below, which I think is the cause of lagging (taking to long). I was wondering if there is a faster way to do this:
...
3
votes
3answers
107 views
A practical example of evenly distributing n lists into a single list
I had previously asked about how to evenly distribute the items in n lists into a single list and was referred to this question.
I made a practical example of my solution for this in VBA for Excel, ...
8
votes
3answers
70 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
3answers
260 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 ...
8
votes
2answers
282 views
Optimize total code in one sub
I have the following code which works fine, but there has to be a faster way to do this. I want to have it all in one sub.
...
4
votes
1answer
62 views
Increasingly Long Runtime for Macro
My code works, but the problem is that it is taking an increasingly long time to run, with the time required to complete calculations increasing every time I use the macro. I've tried a variety of ...
6
votes
2answers
87 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 ...
8
votes
2answers
98 views
No more Fizzbuzz. How about Hello World instead?
Write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of ...
3
votes
2answers
108 views
8
votes
1answer
63 views
Honey I shrunk the view
Going down the rabbit hole, I wanted to be able to change the size of a form, and not have to worry about the layout of the controls.
vba doesn't support user-resizeable forms, but forms still have a ...
7
votes
2answers
81 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
1answer
58 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 ...
10
votes
2answers
103 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 ...
10
votes
1answer
99 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 ...
6
votes
1answer
61 views
Possible memory leak in a for loop macro
I was reading about similar problems to the one I am having, and my guess is that I am having a 'memory leak'. I'm not sure exactly what that means, or how to correct it. Could you take a look at my ...
8
votes
2answers
63 views
How can I make this userform timer faster?
I have a userform that displays a goal time for workers to shoot for when completing a task. It also has a stopwatch on it that is controlled by a start, stop, and reset button on the userform. If the ...
5
votes
2answers
210 views
VBA Function slower than Excel Formula
I've replaced the following horrendous Excel formula (values and names have been replaced):
...
5
votes
2answers
79 views
DRYing up an initialization pattern
I maintain a VBA process that prints a large number of PDFs. It was awful when I inherited it and I've been making improvements over time, but I'm a bit stuck on this one. There are at least 4 ...
7
votes
2answers
114 views
Script for saving/sorting/sending emails based on their subject
I've spent a couple days making this script in Outlook that checks my emails and saves/sorts/sends a reply based on the subject. This also happens to be my first experience in working with VBA. Now ...
4
votes
1answer
93 views
Ignore exceptions when changing column formula in Excel table
The new table feature on Excel is excellent and you can specify a column formula as well as exceptions on the column. However, if you want to change the column formula, Excel will rewrite the whole ...
5
votes
1answer
102 views
Macro for inserting a mini table between heading styles
I am using the following macro to insert a mini table of contents between two identical heading styles (e.g. Heading 1). It works fine on small documents, but is very slow on large documents.
...
5
votes
1answer
60 views
Test if Excel is open without relying on catching an error
Getting a hook on an open instance of Excel is something I do all the time. I'm now questioning if I'm doing this the best way. I'm particularly concerned with the fact that I rely on the ...
