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

3
votes
1answer
45 views

Text files: Copy, Rename, Append/Merge together

I wrote 3 subroutines related to batch data processing, they will be used together. A bit of background, I wrote this for my admin colleagues who do not write code. An application dumps daily ...
3
votes
1answer
20 views

Update on Weave Merging n lists into single list

This is a follow up to my previous post from 7 months ago. I changed up the algorithm a little. Instead of inserting items into a new list, each item's final place is calculated up front. Sort of like ...
7
votes
1answer
54 views

Growing my own Tree (Structure)

Summary VB6 doesn't have a great selection of data structures to work with, so again I find myself creating my own. I have a need to dynamically generate a directory structure on the file system. The ...
4
votes
2answers
84 views

Counting and Deleting Duplicate Data

Can anyone help me optimize the current VBA code below? It is taking 20 seconds currently to collect,store, check and validate 1000 data's. This program takes specific values from multiple sheets and ...
11
votes
2answers
96 views

Follow-up: Randomizing Civilization 5 team choice

This is a follow-up on my previous question about a VBA macro for randomizing a draw from a table. It can be found here: Randomizing Civilization 5 team choice The code has been improved with the ...
15
votes
2answers
272 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 ...
4
votes
1answer
38 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 ...
5
votes
2answers
81 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
76 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
179 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
12 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
44 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
56 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
47 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
148 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
210 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
167 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
84 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
116 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
84 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
104 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
94 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
200 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
336 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
44 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
261 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
72 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
113 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
71 views

Generating fiscal calendar dates

I need to generate a T-SQL script for loading data into this table: ...
6
votes
1answer
94 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
192 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
262 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
106 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
354 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 ...
7
votes
1answer
266 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
140 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
200 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
154 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 ...
12
votes
2answers
684 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
414 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
359 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
359 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
114 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
424 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
364 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
184 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
116 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 ...