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 ...

learn more… | top users | synonyms

1
vote
0answers
8 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 ...
1
vote
0answers
8 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 ...
9
votes
1answer
24 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 ...
0
votes
0answers
16 views

row not retrieving date until line 63 [on hold]

I'm running the following code, but it doesn't start the result until the 63rd row. Not sure how to change it. ...
9
votes
2answers
73 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 ...
8
votes
1answer
64 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
38 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
54 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
176 views

VBA Function slower than Excel Formula

I've replaced the following horrendous Excel formula (values and names have been replaced): ...
5
votes
2answers
67 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
71 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
69 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
74 views

Word macro is very slow, looking for improvements

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. Any ...
5
votes
1answer
48 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 ...
3
votes
1answer
177 views

MS Access VBA code to compare records in a table and combine data - revised

I've previously posted a version of this code here: MS Access VBA code to compare records in a table and combine data where necessary To repeat what I'm looking for from my previous post: Note I ...
4
votes
2answers
126 views

MS Access VBA code to compare records in a table and combine data where necessary

I need to process some data which is the output from a CAD system, namely a Bill of Materials. I've constructed database and written some VBA code to achieve this. An explanation of what this code ...
4
votes
1answer
107 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 ...
6
votes
1answer
73 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 ...
7
votes
3answers
113 views

“Scripting.FileSystemObject” Replacement module in VBA

I have found Scripting.FileSystemObject to be slow and unstable. Calling file_object.Name repeatedly has caused my code to crash excel on multiple instances. ...
7
votes
3answers
72 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: ...
4
votes
1answer
54 views

Recursively searching the Windows Registry

This function is part of a the Registry class that can be found on Google Drive. The class was originally written by Steve McMahon for VB6, but I ported it to VBA ...
5
votes
2answers
96 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 ...
5
votes
3answers
82 views

Improving performance of VBA batch scripts?

I am using a script to open a bunch of Excel files, copying two or more sheets into a new file and saving this new file. It sure beats doing it manually, but I think it could be faster. Here's my ...
6
votes
3answers
114 views

Retrieving data from files in the folder

I am to use VBA code to simply retrieve certain data from all Excel files in certain folder and paste it in working spreadsheet. I am not sure that this is the most efficient (which matters as I would ...
8
votes
2answers
181 views

Calculation of an inflation on volume/year

Background 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 ...
6
votes
1answer
80 views

Traffic light implementation

Can someone review this code for me? I'm not completely sure I did this correctly. I also would like help creating the code for the "walk" "don't walk" lights. I needed to create a traffic ...
8
votes
2answers
58 views

In place quicksort

I implemented my own sorting in VBA because it doesn't provide its own, and it's healthy to test yourself. It was surprisingly difficult and I ended up make a few tweaks that I didn't expect to make ...
5
votes
2answers
89 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 ...
7
votes
2answers
325 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 ...
3
votes
1answer
71 views

Making VBA generated dynamic SQL statements safe against injection

I need to use SQL extensively in VBA macros that I write. Since the DB is from our enterprise application, I use vendor's library for this. I generate dynamic queries, sometimes with many ...
4
votes
1answer
41 views

Ranking a variant array with variable dimensions

I'm doing VBA macros and need to use Arrays of values extensively. After the below help from @TonyDallimore in StackOverflow, I've decided to use nested variant arrays. VBA chrashes when trying to ...
2
votes
1answer
71 views

KeyValuePair implementation

In order to implement my own Dictionary<TKey, TValue> in VB6, I've implemented a KeyValuePair class, which can accomodate ...
6
votes
1answer
148 views

Speed up processing between VBA and IE

I am looking to speed up the exchange from vba to IE. The sendkeys works, but I was curious if there were a better way to do this? The site that it routes to is a form, but there is no submit button. ...
4
votes
2answers
84 views

More efficient update macro in Excel

So I have a macro that makes comparisons and then this macro exports all of the changes based on if the information doesn't match. I have it so that each column gets their own worksheet in the new ...
7
votes
2answers
88 views

Generating a collection of controls

This question I asked previously mentions a function named BuildControlCollection, which I didn't go into the details of since it wasn't relevant. However, because ...
4
votes
1answer
214 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 ...
10
votes
1answer
150 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 ...
6
votes
2answers
120 views

Return index of array where sum first passes n

I have an array of integers and a number n. I want to sum the elements of the array and return the index where the sum passes ...
11
votes
2answers
246 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 ...
10
votes
2answers
227 views

Nullable<T> Implementation for VB6/VBA

Because I was spoiled with C# and the .NET framework, whenever I have to work with VB6 I feel like something's missing in the language. A little while ago I implemented a ...
12
votes
2answers
253 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 ...
4
votes
5answers
287 views

Can I make this VB code run faster?

I need help making this code run faster. I am using it to import a CSV file into Excel. It works, but it is very slow. The file is almost 20MB. Any help is appreciated. ...
6
votes
1answer
149 views

Revisited IsTypeSafe method implementation for “type-safe” List

Following up on List<T> implementation for VB6/VBA, I'd like some thoughts about the revisited IsTypeSafe function, below. The previous version pretty much ...
11
votes
2answers
876 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>. ...
5
votes
1answer
186 views

First VBA project

Here's my first VBA project. I have some very limited Python experience, and this is my first VBA project. I'm sure I could have done it a lot more simply, but I just stuck with what I knew, and ...
4
votes
1answer
66 views

How toTighten up and improve a bastardized Excel VBA code

I have the following code. It's purpose is to add up and sort another columns Data. I tweaked it to fulfil my purpose, but I'm sure it can be cleaned up. The only problem I have with its function is ...
3
votes
2answers
313 views

How can I speed this VBA code up?

I am using VBA to manipulate the global address book in Outlook. My method takes a contact and returns a complete list of everyone who reports through that person based on the Outlook org structure. ...
3
votes
1answer
347 views

How can I get rid of SELECT…CASE and GOTO in this string formatting helper function?

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 ...
3
votes
1answer
118 views

A more readable InStr: StringContains

Consider the following: If myString = "abc" Or myString = "def" [...] Or myString = "xyz" Then In C# when myString == "abc" ...
4
votes
2answers
322 views

Sending test emails to a list of email addresses

I've made the following program that takes a list of email addresses from a table in MS Access and sends each a test email. ...