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
12
votes
2answers
506 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 ...
12
votes
2answers
331 views

Calculation of an inflation on volume/year

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 ...
12
votes
2answers
494 views

Forecast maintenance interval for fleet of aircraft

Being new to VBA, and many years removed from college programming courses in Java, I'm seeking opinions about design, implementation, readability and general better practices using VBA to tackle ...
12
votes
1answer
2k views

VBA Class to persist and restore Excel Application properties

Very often on Stack Overflow, and even on Code Review, I've seen questions and answers that have code that begins by persisting Excel properties like DisplayAlerts ...
12
votes
2answers
123 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 ...
12
votes
2answers
604 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 ...
12
votes
2answers
20k views

String Repeat function in VBA

VBA has built-in functions for repeating a single character: Function String$(Number As Long, Character) As String ...
12
votes
1answer
5k 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 ...
12
votes
1answer
889 views

Functional Framework

First Class Functions So, VBA doesn't support functions as first class objects - no passing functions as arguments or storing them in variables. I eventually found a way to implement function ...
12
votes
1answer
402 views

Shared VB6 *and* VBA extensibility add-in with OnConnect and OnDisconnect handling

Rubberduck works well enough when the VBE is of the VBA variety, but it currently only works when the Rubberduck is configured to load at VBA start-up, and it has a tendency to crash the host ...
11
votes
4answers
724 views

Copying values from one sheet to another

I am trying to copy values from one sheet to another using loop with two conditions and it is very slow. Removing conditions from code doesn't have any effect on the speed of the execution. I already ...
11
votes
4answers
612 views

Get Workbook Method(s)

I'm re-writing my Module of standard methods. These 3 are used to retrieve Workbooks as Workbook Objects. The standard I'm aiming for here is "Third-party Library/Add-in". So: Do these functions ...
11
votes
2answers
160 views

Extract values within date and zone ranges

In order to automate a manual task I created the following code. As I've never written a program to this size, I'm curious if my use of variables, as well as, solutions are the most optimal way for ...
11
votes
2answers
1k 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 ...
11
votes
1answer
628 views

Copy, Paste And Format

I am currently working with this code to automate some tasks for senior staff members that are not very adept in Excel. Wondering if VBA is simply not a very quick code or if my code is clunky and ...
11
votes
2answers
959 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 ...
11
votes
2answers
12k views

Searching a Word Document from Excel

Today I created a script in Excel that migrates data from a specific Word document and copies a portion of it to a cell in Excel (the date to be specific). The file input is Last name, First Name, ...
11
votes
2answers
3k 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 ...
11
votes
2answers
683 views

Solution for Parent/Child circular references: WeakReference class

Sometimes in advanced OOP scenarios, a class needs to hold instances of another class which needs to hold a reference to the "parent". For example when you have a dynamic ...
11
votes
1answer
29k 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 ...
11
votes
1answer
706 views

Standard Methods in VBA [closed]

Since I learnt about refactoring, I've been busy creating my own Module of Standard Methods. I feel it's about time they got a review of their own. Open to all aspects of advice but particularly ...
11
votes
1answer
116 views

Recursive code to generate a parametric sweep of input values

I generate a set of data based off every combination of inputs. The source range that's inputs requires headers to identify the parameters. In its present state I'm not content as it's not as ...
11
votes
1answer
275 views

BattleShip Grid: Classes and Tests

In my ongoing quest to demonstrate how VBA code can absolutely be object-oriented, I've started implementing a game of Battleship in pure VBA. This is a rather large project, so I'll split the ...
11
votes
1answer
1k views

Enumerable Custom Collections in VBA with Dictionary features like Exists

To Collect or Hash The VBA.Collection has a number of limitations, but it is enumerable and you can refer to items by index or key. But the VBA implementation of a ...
11
votes
2answers
4k views

Managing a programmatically accessible stack trace

VBA has a call stack... but there's no programmatic way to tap into it, which means in order to get a stack trace for a runtime error, one has to manage it manually. Here's some example code that ...
11
votes
1answer
122 views

Function to find the kth match

I've created a UDF some time ago and used it pretty often till now in different ways (but mainly to compare a "history" of data like having different filters at the same time). While there is no need ...
11
votes
1answer
224 views

Find all differences between 2 strings

I pulled this out of my code bucket and dusted it off earlier today in response to a post over on SO that made me cringe. This was originally written to highlight changes in Excel cells in real time ...
11
votes
1answer
176 views

Yet Another Fraction

A recent question inspired me to implement a Fraction class. I decided to write this one in vba, because I like writing tools for the poor souls that still have to ...
11
votes
1answer
1k views

Drawing Fractals with MS Excel VBA

I've made Excel VBA code that draws and fills a fractal pattern. I know there's a few more ways to optimize the code, just glad it works right now. ...
10
votes
3answers
1k views

Macro that does some kind of duplicate check

This macro pulls in two workbooks, one being a template with saved formulas already, and the other containing data with thousands of rows...I need to increase the speed because the process takes more ...
10
votes
5answers
956 views

Macro code that removes excess rows

I'm a business student who just started to learn VBA. I am trying to write a macro for a project but only have minimal experience actually stepping into the code. The macro I have written is to ...
10
votes
3answers
435 views

Simulated WeakReference class

I've created a fake WeakReference class for VBA by chance. A Weak Reference is not counted (i.e. IUnknown::AddRef method is not called). I knew about the Weak Reference concept from Swift and I ...
10
votes
4answers
3k views

Create a list of random numbers with sum = n

I want to create a list of random integers in the range [0, 9], that sums up to 100 in Excel, using VBA. The list should be printed in a single column. The routine I've written is as follows: ...
10
votes
2answers
816 views

Conditionally deleting rows in a worksheet

I have used loops to remove lines that are doing various things, but all are deleting rows where certain criteria is met. Some are deleting less than certain numbers, deleting blanks, removing ...
10
votes
2answers
1k views

Inline error checking in VBA

I asked a question elsewhere on Stack Exchange and was given an answer by multiple people that checking for errors in-line was not a good practice. I have been using an ...
10
votes
3answers
1k views

Cleaning up and reformatting imported data in an Excel sheet

The code below was refactored for performance improvements for another user on this site. Functionality, high level: Sheet1 - CodeName aIndex: used as the main reference to the structure of the data ...
10
votes
2answers
197 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 ...
10
votes
3answers
220 views

Userform for getting data from data sheet into a table

I am making a userform that grabs data from a data sheet and puts it into a table: Grabs the data based on what the user wants (Brand -> Items for brand) Allows multiple items to be added Displays ...
10
votes
2answers
10k views

Sorting a collection

Related to, but not exactly a follow up of this question. After fixing some issues discovered in the last review, I added a little more functionality to the ...
10
votes
3answers
698 views

More imitation of Enumerable in VBA

I was inspired by Me How's question to see how far I could push an imitation of .Net's Enumerable Class. The new functions can obviously handle Collections, but can also handle any collection-type ...
10
votes
1answer
4k 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 ...
10
votes
3answers
756 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 ...
10
votes
2answers
398 views

Financial Data From Webqueries in Excel

I'm new (to CR and to programming in general). I wrote my first VBA on Monday. This is my first working project. It Takes a bunch of financial data from a company called Financial Analytics and a ...
10
votes
2answers
760 views

Userform Event Listener and Emitter

It has been a royal pain that MSForm controls are missing simple events such as MouseOver and Blur, and I find myself often ...
10
votes
2answers
764 views

Basic Imitation of C# Enumerable in VBA - or any other static class

Since implementing static classes in VBA actually seems possible I came up with an idea to try to imitate C#' Enumerable class (well, to some extent...). This is ...
10
votes
1answer
8k views

Import multiple XML files into Excel

I have adapted a VBA code to import all the XML files I have on a folder into Excel. I have tried to run the code with only two files and it takes about 30 seconds. Several minutes to import less than ...
10
votes
1answer
114 views

Aggregating 2 lists by matching UniqueIDs

This Macro takes my company's 2 main sources of all-client data (each a data table in a separate worksheet) and aggregates them into a 3rd list, by matching a unique account number (for clients that ...
10
votes
2answers
172 views

Disposable Heroes

I had a bit of an issue with my last piece of code, having to do with cleaning up resources. I needed a way to ensure the database connection was always properly closed, even if there still were ...
10
votes
1answer
496 views

A simple ascii progress bar

I've been wanting to experiment with a bit of error handling and robustness to make my code more user friendly. I was inspired by this answer on UX to try and add a progress bar to a button - but was ...
10
votes
2answers
362 views

Creating a pseudo Pivot Table / Database using a 4-D array

Why am I not just using a Pivot Table / Database? a) I've never ever used either before. And I don't have time to learn how before this project needs to actually be finished. b) The final output ...

1
2
3 4 5
25