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

9
votes
1answer
23 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
14 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
66 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
61 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
36 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
53 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
175 views

VBA Function slower than Excel Formula

I've replaced the following horrendous Excel formula (values and names have been replaced): ...
5
votes
2answers
66 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
70 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
68 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
46 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
167 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
121 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
106 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
112 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
77 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
86 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
312 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
146 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
83 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
87 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
205 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
144 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
245 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
226 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
285 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
868 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
181 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
310 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
346 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
318 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. ...
2
votes
1answer
180 views

Dynamic ranges and page-formatting

I've put together the code below, which creates a new sheet in my workbook and applies dynamically-named ranges and page-formatting. ...
4
votes
2answers
125 views

Procedure too long. Cannot execute. How to make it shorter and more effective?

My code is too long and cannot be executed. It simply searches for an empty row in a given destination. If it is empty, it performs certain actions (copying, pasting, etc.). If it is not empty, it ...