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
10
votes
1answer
425 views

Unit test code for Antlr Grammar

I'm working on a grammar for the Visual Basic for Applications (VBA) programming language. I've discovered a way to make assertions about how a parse tree should be generated by using the Antlr ...
10
votes
1answer
250 views

Produce a Linear Cut List for Woodworking

I was inspired to write this by a recent review of cutting pipes. The only thing I'm really iffy about is using the WriteCuts method. I couldn't figure out a way to ...
10
votes
0answers
192 views

Host-Agnostic Model-View-ViewModel Infrastructure

I've written an API that unlocks some serious MVVM capabilities with VBA's MSForms UI framework. This is the complete code-behind for an example ...
10
votes
0answers
2k views

Function to conect to Firebase Rest API via Access VBA

This is the application for small school, which will store students' info in Firebase and also retrieve data from Firebase. I am using JSON Parser to parse the data and save the same in Access tables. ...
9
votes
3answers
3k views

FizzBuzz in VBA

FizzBuzz in VBA. Takes start and end numbers as arguments. Will handle any pair of integers to +- 2 Billion or so, determine whether the sequence is increasing or decreasing and output numbers to the ...
9
votes
2answers
342 views

Optimize total code in one sub

I have the following code which works fine, but there has to be a faster way to do this. I want to have it all in one sub. ...
9
votes
3answers
207 views

Check Accounting Journal for Errors

I wrote this sub in Excel VBA. I am a novice, but I was able to get my code to work how I wanted it to. Unfortunately, I can see there is a lot of repetition, but it is too complex for me to ...
9
votes
2answers
1k views

Using VBA to “apply names” to named range references

So there's a known bug in excel where applying named ranges via ribbon doesn't work properly (for 2010 and 2013 and possibly 2016). There was a question about it on superuser - Apply names in excel ...
9
votes
2answers
2k views

Extracting Country Names from Cell Values

This fairly clunky looking VBA script successfully pulls names of countries from an Array, but could it be made shorter? Mass of text from Column C onward in each row may contain the names of ...
9
votes
4answers
900 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: ...
9
votes
1answer
5k views

Multi-dimensional array sort and filter functions

As I said in my last post Multi-dimensional ascending and descending sort array function, I'm back with the final version of the code. Added features: Filter array function by following parameters: <...
9
votes
2answers
548 views

Write to EXCEL from SQL DB using VBA script

I have a vba code which takes data from SQL database and dumps it into excel. I see that my query should extract approximately total of 120k records. I monitored this activity and learnt that even ...
9
votes
3answers
2k views

Web Scraping with VBA

I wrote this to scrape album review data from AOTY into a spreadsheet. Check it out and let me know what I could've done better. ...
9
votes
3answers
772 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 ...
9
votes
2answers
4k views

Higher Order Functions in VBA, the dirty way

I have been building a small library of VBA functions. I have a small section with higher order functions that I'd like to share. It uses strings as delegates/function pointers and ...
9
votes
2answers
17k views

Powershell script for manipulating Excel files

I have a directory of .xls workbooks with the following naming convention: 001.WIP Monthly Report 002.WIP Joes Custom Report ... 129.PUR Supplier Spend The ...
9
votes
2answers
243 views

Matching between files and a list of filenames at scale

Given a folder typically containing ~250,000 small (20-100kb) files in HTML format (they open as single-sheet workbooks in Excel) and a list of ~ 1 million filenames, I need to open all the files that ...
9
votes
2answers
249 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 ...
9
votes
1answer
203 views

Class containers, filtering and coding practices

Context Usually, when I have to deal with a collection of items (especially objects) I tent to create a container class for that collection (wrapper?). What I mean by that is: when I have to handle ...
9
votes
2answers
295 views

Generating fiscal calendar dates

I need to generate a T-SQL script for loading data into this table: ...
9
votes
2answers
1k 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 ...
9
votes
3answers
258 views

Remove unwanted characters from specified column in all Worksheets

I need to remove some unwanted characters from a given column in all Worksheets from my Workbook. The code below works perfectly, but I'd like to know if there is a better/cleaner/faster way of doing ...
9
votes
2answers
962 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 ...
9
votes
2answers
588 views

No more Fizzbuzz. How about Hello World instead?

Write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both ...
9
votes
1answer
915 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 ...
9
votes
1answer
169 views

Secure ADODB: Unit of Work

I've been meaning to revisit this old code for a long time, and this week finally ended up doing it. The resulting code is on GitHub, and for full context and disclosure I wrote a blog article about ...
9
votes
1answer
3k views

Excel VBA Multiplayer Tetris Game Loop Repaint Rate

While searching VBA Excel Tetris games online, I noticed several single player Tetris games but no multiplayer games. I am looking for a better strategy to manage the speed of the Application and ...
8
votes
5answers
2k views

Copying and comparing two sheets before deleting duplicates

I am running this code as a macro in Excel, which copies two columns from sheet 1 and pastes them into sheet two. It then compares the first column to a column in sheet two before deleting any ...
8
votes
4answers
11k 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 ...
8
votes
5answers
958 views

Managing book of Excel sheets

This is a follow-on from a previous question I posted here. I've got code here that works for what I want, but the problem is the loop takes ages to perform. I was wondering if anyone could follow ...
8
votes
3answers
2k 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. ...
8
votes
3answers
163 views

Loop through a unique combinations driven by choices

I am trying to optimize my code and make it as fast as it can be, I have researched as much as I can and am now asking the community for any tips because there are definitely people more experienced ...
8
votes
4answers
194 views

Looks at order data and arranges it to focus on order locations

I have a sheet that order a bunch of information on individual orders. Order numbers are in column A, other information is in columns B-P, and then product numbers are the headers for columns Q-DL. ...
8
votes
4answers
181 views

Saving worksheets as pdf and sending per mail

As the year comes to an end our company need new time-sheets for the new year. Because this is my task to do, I thought I would do my collegues something good by automating some tasks they would ...
8
votes
4answers
2k views

Converting from .xlsx to .txt

I have code that converts each sheet of a spreadsheet into a .txt file. It works well, however, given the big number of exports (about 90 .txt files), I'd like to seek advice on how to speed it up. <...
8
votes
3answers
290 views

Colorizing Excel cells with a date range filter

In a table there is a number of column (chambre) and a number of row (alveole). If these matches in a list in an other sheet then it checks if it the date is between two dates on that second sheets. ...
8
votes
2answers
7k views

Word VBA function to find a specified heading & return its text as a string

In this post, I asked for an overall review of a script to auto-generate table captions, because it was running relatively slowly. I've used Debug.Print to isolate the problem, and this function takes ...
8
votes
3answers
160 views

Clearing duplicates from an Excel 2007 sheet

This is the original code I had for removing duplicates from a sheet: ...
8
votes
1answer
145 views

Data entry of Information

Couple of items to make note of first: Some of the code is inherited the other has been refactored by me. The procedures that have an underscore in them I am still working on refactoring. I am still ...
8
votes
3answers
334 views

Speed up VBA with 138k rows and ~330 sheet creation

I have a VBA script that does the following and I am trying to see if I can have it perform faster than 44 seconds: start with ~138k rows of data on sheets("Data") concatenate each cell in the row ...
8
votes
3answers
323 views

If ComparisonIsTrue(thisPost, ComparisonOperator.NotEqualTo, goodCode) Then Me.Answer

This is a follow-on to a global enum for comparison operators. I wanted to pass a logical expression E.G. >= someValue as a parameter to a function, so I made an ...
8
votes
2answers
125 views

Function to find the kth match (2)

code from suggestions in comments here. I will still write it like the first time, so you do not need to read the old question. I've created a UDF some time ago and used it pretty often till now in ...
8
votes
2answers
376 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 ...
8
votes
3answers
191 views

Turn logged data into fancy plot

I'm logging data from an embedded platform over UART. This gets saved to a log file and I want to process the saved log with Excel. I have no idea whether what I did was even remotely a good solution....
8
votes
2answers
196 views

Report Building (Data Retrieval, Validation, Aggregation, Business Logic, Report Building, Visual Presentation)

This: Is a data table we get from our financial platform with lots of useful information. For reference, "--" is also the string they use to denote empty values. This: Is a spreadsheet I built (...
8
votes
1answer
141 views

VBA - new built-in enumerations and backwards compatibility

msoGraphic is an MsoShapeType enumeration that is available in Office PowerPoint 2016 and later (versions which can handle svg graphics). If you attempt to check an MsoShapeType against msoGraphic in ...
8
votes
1answer
111 views

Pasting Values To/From a Calculation Model

I have a Database worksheet (SoapUI - CollisionsSingle) that have around 1500 records and 38 columns of data per record. I have a VBA script that copies the records ...
8
votes
2answers
9k views

Fast(er) web scraping with VBA

I have a code that fetches rates from a website called X-Rates, and outputs to excel the monthly averages of a chosen country. The code runs quite fast, but I still think I could improve the code a ...
8
votes
2answers
3k views

Converting Data in Rows and Columns to Rows in VBA for Excel

I have a working VBA script for Excel that converts a matrix of data with multiple records in per row to multiple rows with one record per row. A StackOverflow user told me that the code could use ...
8
votes
2answers
215 views

Aggregating data from multiple worksheets into a consistent format

First off, this is the 3rd (and probably last) review of the project in question. You can find the previous question here. The Macro accesses a workbook containing 8 worksheets each with similarly ...

1 2
3
4 5
25