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
votes
0answers
19 views

Access VBA loop freezes after 10 seconds [on hold]

I am assigning representatives at a call center to a list of call recipients (members). However, I am grouping the members by household. I am trying to assign reps using a loop through the member ...
3
votes
1answer
21 views

Key on a Header and Offset

I have a section of code that I've been attempting to clean up and hopefully speed up. The section of code where I pass values in order to generate a key and update a table on a different sheet I ...
4
votes
2answers
57 views

Replacing a For Each loop with something faster

I'm a very inexperienced VBA user, I've just created my first "For Each" loop but it's very slow. I'm happy that it works but eager to know if there is a alternative method that is quicker. Any ...
6
votes
1answer
67 views

Quicker way to go through 550 variables with very similar code

I'm hoping to figure out a faster way to write this before I resign myself to copying and pasting 500 times. I have code here that needs to do tons of vlookup matches. The only thing that changes ...
5
votes
2answers
131 views

Finding matching PDF files in different folders and merging the two files into one

I have code which currently loops through one folder, finds two matching pdf-filenames and merges them into 1 pdf-file always in the same order. File 1 then File 2. My code is slow, maybe because ...
1
vote
0answers
9 views

Average interval between dates with random blanks updated

After getting some help at Average interval between dates with random blanks, I rewrote most of the code. Here is the updated code: I have a spreadsheet with order date data: I need to find the ...
1
vote
0answers
39 views

Setting “Find(What:=” to a column in a different .xls file [closed]

I currently have code that opens up an empty workbook and then a master file with 5000+ rows of info. A pop up then appears and asks for a ID. I type in my 6-digit ID and the macro goes through column ...
3
votes
1answer
46 views

Representing Objects as strings in VBA

I am working an a more meaningful way to print objects in VBA. The desired result should look something like this. ...
4
votes
1answer
46 views

Average interval between dates with random blanks

I have a spreadsheet with order date data: I need to find the average interval in days between each order date. I have to both find a way to get past the blank cells in the row, and also take into ...
2
votes
1answer
128 views

Calling Stored Procedures with Lots of Parameters

I have this piece of code for running a Stored Procedure, and I was wondering if there is a way of cutting code like: ...
8
votes
2answers
201 views

Efficiently create and sort a Collection

The goal of my code is to sort data into two categories. It must use a local copy of the initial data from Collar (Top View).csv. My code creates a Collection of items called Collars using the initial ...
5
votes
1answer
123 views

Copy Cell on Worksheet_Change event if some criteria is met

This code is working fine, but it looks awful (yes, I am a rookie on this thing). How can I improve it to look better? ...
5
votes
4answers
69 views

Modifying excel code to speed up the process

I'm currently trying to create a macro, to edit a document that is around 280k rows and 12 columns. For the first steps I found a macro to delete the rows I don't want based on certain criteria, but ...
4
votes
2answers
109 views

Copying cells from one sheet to another

I'm new to writing VBA and having spent most of the day writing some code, I have finally got it working but it looks horrible. I'm hoping that someone can help me tidy it up. The code simply copies ...
4
votes
1answer
73 views

Converting to Roman Numeral with Recursive Algorithm

Summary I'm getting ready to dive back into a "more proper" project, so I wanted to take a moment to get my TDD hat on before doing so. I decided to tackle this Roman Numeral Kata for practice. The ...
10
votes
2answers
96 views

Karate Chop Kata

I had some time to kill today, and I found the Karate Chop Kata. Specification: Write a binary chop method that takes an integer search target and a sorted array of integers. It should ...
4
votes
2answers
89 views

Retrieving stock prices

It takes around 5-8 seconds for me to retrieve a previously-closed stock price and a dividend rate from US Yahoo! Finance. If I wanted to retrieve 10+ stock prices, it would take me more than a minute ...
5
votes
2answers
67 views

Join two collections by date with no duplicate

Data I have 4 Collection objects of the following kind: ...
5
votes
3answers
161 views

Building an OPC client in Excel

I am pretty deep into building a very multi-faceted little app in Excel with VBA. It does a number of tasks, all centered around using OPC to get tag values from several PLC's and doing various ...
7
votes
1answer
297 views

Improving performance of a VBA code to read contents of many .CSV files

I'm fairly new to VBA and just completed my first script. It's completing the task I want it to do just fine, but it's really slow* in doing so. It has to open and read about 1000 csv-files, for each ...
6
votes
2answers
43 views

Cascading Changes to Future Entries in a Schedule

I've been working on a scheduling application and I have the middle tier completed at this point. It's not changed in a few days, so I feel it's ready for review. I have just this one routine that ...
6
votes
1answer
202 views

Run arbitrary INSERT INTO SQL Query using parameters

While DoCmd.RunSQL is all well and good for simple code, the number of times I've run into problems with unescaped apostrophes and the like was starting to nark a ...
5
votes
1answer
61 views

Conditionally copying cells from other worksheets

I wrote super redundant VBA code and it works and solve my problem. I am trying to simplify it but don't know where to start due to my limited knowledge in VBA. Basically, this code works as a ...
7
votes
1answer
103 views

Access function to return “number of working days until date” for form

I need a function I can put on a form which does: Calculates number of working days until a given date Excludes weekends Excludes any dates in a "holiday" table This is replacing something which ...
6
votes
2answers
67 views

Generating fiscal calendar dates

I need to generate a T-SQL script for loading data into this table: ...
6
votes
1answer
80 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 ...
12
votes
3answers
165 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 ...
5
votes
2answers
194 views

VBA ClickBot featuring AJAX waiting and Element searching

If you work with a database that you only have access through a web interface, this one is for you. You probably don't have access to any programming languages other than VBA through MS Office, which ...
6
votes
2answers
103 views

Immutable Linked List in VBA

I made an immutable list class using the head-tail idiom. If I did this correctly, it implements persistent data structures. Unfortunately it doesn't scale well ...
6
votes
1answer
291 views

Excel VBA binary search class module [closed]

I have a long list of sorted strings in a Workbook, and I have to find a lot of values in it. I was hoping to build a binary search Class Module that will find them for me faster than Excel's built-in ...
6
votes
1answer
196 views

Overriding all Access form control AfterUpdate methods

I have a set of custom event handlers for all my textboxes and combo boxes. The reason for this is my users want the bound form (to SQL Server) to immediately push changes to the recordset, rather ...
10
votes
2answers
131 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, ...
5
votes
3answers
172 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 ...
4
votes
2answers
136 views

VBA Excel Game - Testing

My brother bet me that I couldn't make a game in Excel. The code is all over the place and buggy in parts. But I would appreciate it if anyone would give this game a go. The music and the code has ...
11
votes
2answers
541 views

StringBuilder in VBA

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

Insert variable number of rows

In the Excel vba sub below, I need to insert the number of rows equal to variable j after row k. So if ...
7
votes
1answer
47 views

Joining collections… because it's fun

I'm working on a LinqEnumerable class (reviewable here), and I'm no Big-O expert and I'm pretty bad with algorithms, so I wonder what the complexity is for what ...
21
votes
2answers
335 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 ...
10
votes
1answer
262 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 ...
8
votes
2answers
106 views

Generating and calling code on the fly

Delegate This class module defines what I'm calling, in this context, a Delegate - here a function that can take a number of parameters, evaluate a result, and ...
5
votes
2answers
317 views

Checking for missing values in several columns of a spreadsheet

Basically I had 3 columns of Data Adjudicated Cases Not Adjudicated Cases All Cases and I wanted to get a list of all the cases that were missing from Column A and Column B, with a little help ...
6
votes
4answers
281 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 ...
10
votes
2answers
181 views

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 ...
11
votes
1answer
103 views

Extensible logging - DatabaseLogger

Recently I wrote a logging API that features an ILogger interface. I wanted to extend my library with a DatabaseLogger ...
14
votes
1answer
114 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
30 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
68 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
150 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 ...
10
votes
3answers
158 views

Ugly workaround to get the vbext_ProcKind of a procedure is breaking encapsulation

This is a follow up to Extending the VBAExtensibility Library. It turns out that code had a really nasty bug. Anytime vbeProcedure.StartLine got called, I was ...
8
votes
2answers
152 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 ...