Questions tagged [vba]

Visual Basic for Applications (VBA) is an event-driven programming language 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 and over 200 non-Office hosts.

Filter by
Sorted by
Tagged with
7
votes
2answers
391 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 ...
7
votes
2answers
401 views

Output the average values of binned columns in Excel VBA

This code is meant to run through a column of values, bin the values based on specified ranges, then output the average value of each bin. The problem is the code is running quite slowly (...
7
votes
2answers
122 views

Analysis tool for a log file

I've written a bit of code designed to go through a log file and determine successes and failures for certain criteria for each server in an environment. The code works just fine, except that on ...
7
votes
1answer
2k views

Extracting data from a Word document is too slow

I have a 100page long .docx format document. I'm using a macro written in VBS to extract some information and then just generate a table from them. I iterate through the paragraphs and store the ...
7
votes
2answers
376 views

Part 1: Send HTTP request for each row in Excel table

Update: This is an older version of the question/script. The new version can be found here: Part 2: Send HTTP request for each row in Excel table I have an Excel spreadsheet where users can batch-...
7
votes
1answer
334 views

Excel VBA Explanation/Optimisation Needed

I am a self taught VBA user. I was asked to look at the code from a tool which has stopped working due to a data overflow and "fix" it. I was told the code was 'optimised' so that it took only 2 hours ...
7
votes
1answer
443 views

Audit Updating Macro

In my code, I'm trying to manipulate data for an audit between 3 sheets in a workbook.The first block of my code is to paste the data of items I need to find for the audit from the original sheet onto ...
7
votes
3answers
10k views

Most efficient multi-find/replace solution in Excel VBA

I'm looking to improve the runtime and efficiency of my VBA code that performs multiple Replace operations on an Excel Spreadsheet. I started out with the following ...
7
votes
1answer
2k views

Finding and pasting images into a specific cell

The below macro finds and pastes images into column A. While the macro works, it starts to slow down when running 500+ images. I am not too familiar with the VBA language, does anyone have any ...
7
votes
1answer
2k views

Reading contents of a thousand 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 ...
7
votes
1answer
5k 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
3k 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 ...
7
votes
1answer
2k views

How to make this ping test with timer more efficient?

I wrote a small ducktaped VBA script which pings servers every 15 mins or so. If a server's status is anything other than "Alive", the server and timestamp is written to another worksheet called "Log"...
7
votes
1answer
178 views

Sudoku-lite challenge

You can find the problem here. The example: Column A, B, and C are each 3 and together equal 9: ...
7
votes
1answer
1k views

Scraper for a lazy-loading webpage

I've written a script in VBA in combination with selenium which is able to scrape all the 1000 links from a lazy-loading webpage which displays it's content 20 at a time and doesn't display the full ...
7
votes
1answer
139 views

Create a table that lists macros in a workbook or worksheet

I'm updating an Excel workbook that I've inherited and had to figure out if/when/where Functions/Subs were being called. Ran into instances where in the code I couldn't find it being called and ...
7
votes
1answer
279 views

Comb Sort in VBA

Playing off Robust Bubble Sort in VBA and as suggested by @Henrik, I took a look at comb sort and tried to create an algorithm based on the documentation solely on Wikipedia. Basically, the first ...
7
votes
2answers
151 views

Parsing 4 Million Filenames

This is the next step in my project to query Companies House records. The first step, retrieving and validating the company numbers to be targeted, was covered here and I will include that code at the ...
7
votes
2answers
560 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
192 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 (...
7
votes
1answer
219 views

Private VBA Class Initializer called from Factory

I've been playing with COM lately and while getting to understand the mechanism of how class methods/properties are called an idea came to mind: what if we can have a global instance of a class that ...
7
votes
3answers
85 views

Evaluating Dice Poker results

In Dice Poker (or Yacht), a player rolls 5 standard dice (sides 1 - 6) and the resulting roll is given a score based on combinations: Two matching dice only - pair Three matching dice only - three ...
7
votes
1answer
440 views

Creating dialog with Access form instances

With Access forms, creating a true dialog form -- in the sense of actually pausing execution of the calling VBA code -- with only instances (rather than using global default instances) is a ...
7
votes
1answer
458 views

Get data from user input as workbook or worksheet

I had two different functions for populating a data array. I tried to refactor them using a single argument as to whether or not the user should be selecting a worksheet or just the workbook. The ...
7
votes
1answer
3k views

Excel VBA to parse JSON out of Google Maps API

Some code that I wrote and broke into 5 separate questions on stack overflow. Some guy wanted to query Google Maps receive some JSON and then for each result found within that JSON do another query. ...
7
votes
1answer
96 views

Interpolating data of multiple trains

I have written a piece of VBA code to essentially replace a complex formula that I was using in the Excel cells. I have the data on Sheet2, which describes multiple train journey's along the same ...
7
votes
1answer
121 views

Data table retrieval and indexing (+ interfacing between workbooks)

This project utilises the external-workbook-retrieval methods found here for the second workbook. This is the beginning of a project. The code here covers: Initial setup (define workbooks, unmerge any ...
7
votes
1answer
959 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. ...
7
votes
1answer
224 views

VBA Class converter to eliminate strings being used as valid values

I'm trying to avoid strings being passed around as a valid value. The ability to make a mistake is too high for my taste and the solution that I've come up with is to use an ...
7
votes
1answer
807 views

Custom sorting an Excel Data Table (+ visual formatting)

I'm (re)building a data table to track our clients that receive regular income payments. Specifically, I need to pull this data into other workbooks for other reports, and since I was here anyway I ...
7
votes
1answer
117 views

VBA-Sync code feedback

I was hoping to get some input on a class module I'm designing to abstract away the boilerplate of utilizing asynchronous queries in VBA. cQueryable supports both synchronous and asynchronous queries. ...
7
votes
1answer
205 views

A Moses's worthy string splitter

I am trying to perfect my string splitter's performance, to be more fast, more easy to maintain if someone else reads it and more readable code-wise. Context ,Scope and Objectives Where I work we ...
7
votes
1answer
181 views

Class: 2D Variant Array

Note: Yes. It's big. I'm not expecting commensurately long/detailed answers (though if anyone wants to write one, you'll definitely be receiving a substantial bounty). This class is going to be used ...
7
votes
1answer
2k 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 ...
7
votes
1answer
1k 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 ...
7
votes
0answers
2k views

Responding to Events of Dynamically added Controls

I was asked for an example of a class to wrap dynamically added controls as I suggested in this Answer. Although it is out of the context of a Code Review, I thought that it would make an interesting ...
7
votes
0answers
252 views

Web Battleship -The Unofficial Battleship UI

I've written my own UI for Mathieu Guindon's VBA Battleship (Battleship UI: GameSheet) which uses a webpage in a WebBrowser control for the View. Webform: Userform ...
7
votes
0answers
1k views

Bringing The System.Text.StringBuilder Up To Lightning Speed In The VBA

After having to swap the feature rich System.Text.StringBuilder for the very fast but very vanilla A lightning-fast StringBuilder, I decided it was time create a ...
7
votes
1answer
160 views

Split cells vertically on comma

This takes a field delimited by commas, and splits on the comma. The newly divided information is put in another field (created at the start of the program) called ...
6
votes
4answers
1k 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, ...
6
votes
4answers
3k views

Copying data from an Excel sheet to Word

This code copies a specific range and a chart from an Excel sheet to Word. This code works fine but it is very slow. I have used delays(Wait) to work properly while ...
6
votes
5answers
340 views

Simplify & Reduce steps in the IsDistinct function

Because I often have to deal with two Variants that may or may not be Null, we need a null-safe equality test, so I came up with ...
6
votes
2answers
549 views

Next revision letter

This is a fun one! I wrote some code to automatically get the next revision letter for a file by looking at the existing letter. It works, but I think there is room for improvement as far as ...
6
votes
3answers
278 views

Remove blank entries from row

I wrote a sub to remove the blank entries in a row without shifting the cells around but it seems unnecessarily clunky and I'd like to get some advice on how to improve it. ...
6
votes
2answers
7k views

Code to find value into a table by 2 criteria (x,y)

My code finds the value that corresponds to two values in a table. It works fine, but I'd like to know what you might do differently. Table sample: Input: ...
6
votes
3answers
12k views

Is it a bad programming practice to declare variable in a loop?

Today, I came across a code example like the following: ...
6
votes
3answers
1k views

Test for Array vs Range in my TEXTJOIN UDF

I use the following code in many of my answers on Stack Overflow, to mimic the new TEXTJOIN function available in Office 365 Excel: ...
6
votes
2answers
1k views

Find cells with specific formatting

I'm currently writing code so that when a red-colored cell appears in a column, a message box will appear letting me know that column has an error. I have written the following method: ...
6
votes
3answers
3k views

Simple Caesar Cipher Function

For no particular reason, I wanted to create a function that would take a string and "encrypt" it via Caesar cipher. This function takes a string and shifts the letters left or right (in the alphabet) ...
6
votes
3answers
441 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 ...

1
3 4
5
6 7
25