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 ...
0
votes
0answers
15 views
out of memory error for find and replace colors macro [on hold]
I created a basic find and replace macro for colors. Users can either select a color to find and a color to replace by selecting cells or from the color palette. The macro runs correctly, but after it ...
7
votes
2answers
56 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 ...
4
votes
1answer
55 views
Ignore exceptions when changing column formula in Excel table
The new table feature on Excel is excellent and you can specify a column formula as well as exceptions on the column. However, if you want to change the column formula, Excel will rewrite the whole ...
5
votes
1answer
70 views
Word macro is very slow, looking for improvements
I am using the following macro to insert a mini table of contents between two identical heading styles (e.g. Heading 1). It works fine on small documents, but is very slow on large documents.
Any ...
5
votes
1answer
43 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 ...
3
votes
1answer
87 views
MS Access VBA code to compare records in a table and combine data - revised
I've previously posted a version of this code here:
MS Access VBA code to compare records in a table and combine data where necessary
To repeat what I'm looking for from my previous post:
Note I ...
4
votes
2answers
70 views
MS Access VBA code to compare records in a table and combine data where necessary
I need to process some data which is the output from a CAD system, namely a Bill of Materials.
I've constructed database and written some VBA code to achieve this. An explanation of what this code ...
4
votes
1answer
73 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 ...
6
votes
1answer
64 views
File reader/writer (text)
Following-up on this post, I wanted to be able to use my FileWriter with a syntax reminiscent of .net's using blocks, which ...
7
votes
3answers
93 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. ...
7
votes
3answers
72 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:
...
4
votes
1answer
49 views
Recursively searching the Windows Registry
This function is part of a the Registry class that can be found on Google Drive. The class was originally written by Steve McMahon for VB6, but I ported it to VBA ...
5
votes
2answers
88 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 ...
5
votes
3answers
74 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 ...
6
votes
3answers
107 views
Retrieving data from files in the folder
I am to use VBA code to simply retrieve certain data from all Excel files in certain folder and paste it in working spreadsheet. I am not sure that this is the most efficient (which matters as I would ...
8
votes
2answers
181 views
Calculation of an inflation on volume/year
Background
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 ...
6
votes
1answer
65 views
Traffic light implementation
Can someone review this code for me? I'm not completely sure I did this correctly. I also would like help creating the code for the "walk" "don't walk" lights.
I needed to create a traffic ...
8
votes
2answers
57 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 ...
4
votes
2answers
72 views
Materializing any ADODB Query
Following-up on Creating ADODB Parameters on the fly and pushing the "wrapping" of ADODB a step further, I have written two more classes that allows me to expose methods that don't require a ...
7
votes
2answers
216 views
Creating ADODB Parameters on the fly
I have put together a small wrapper class to simplify creating parameterized ADODB queries with VB6/VBA. At this point I'm keeping things simple, so it's only supporting input parameters and from what ...
3
votes
1answer
61 views
Making VBA generated dynamic SQL statements safe against injection
I need to use SQL extensively in VBA macros that I write. Since the DB is from our enterprise application, I use vendor's library for this.
I generate dynamic queries, sometimes with many ...
4
votes
1answer
39 views
Ranking a variant array with variable dimensions
I'm doing VBA macros and need to use Arrays of values extensively. After the below help from @TonyDallimore in StackOverflow, I've decided to use nested variant arrays.
VBA chrashes when trying to ...
2
votes
1answer
62 views
KeyValuePair implementation
In order to implement my own Dictionary<TKey, TValue> in VB6, I've implemented a KeyValuePair class, which can accomodate ...
6
votes
1answer
122 views
Speed up processing between VBA and IE
I am looking to speed up the exchange from vba to IE. The sendkeys works, but I was curious if there were a better way to do this?
The site that it routes to is a form, but there is no submit button. ...
4
votes
2answers
82 views
More efficient update macro in Excel
So I have a macro that makes comparisons and then this macro exports all of the changes based on if the information doesn't match. I have it so that each column gets their own worksheet in the new ...
7
votes
2answers
82 views
Generating a collection of controls
This question I asked previously mentions a function named BuildControlCollection, which I didn't go into the details of since it wasn't relevant. However, because ...
4
votes
1answer
142 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 ...
10
votes
1answer
121 views
Importing data from an external EXCEL-Sheet
I was assigned the task of copying some subsums from a given EXCEL-Sheet into the executing EXCEL-Sheet.
This had to be done with an EXCEL-Macro, so non-programmers can easily use it. There was the ...
6
votes
2answers
110 views
Return index of array where sum first passes n
I have an array of integers and a number n. I want to sum the elements of the array and return the index where the sum passes ...
11
votes
2answers
238 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 ...
10
votes
2answers
218 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 ...
12
votes
2answers
248 views
Avoiding repeated code in worksheet
Can someone help me make this code more professional? I'm trying my best to find something similar, but I wasn't successful.
I want to avoid the repeat of the code for every single value and also I ...
4
votes
5answers
256 views
Can I make this VB code run faster?
I need help making this code run faster. I am using it to import a CSV file into Excel. It works, but it is very slow. The file is almost 20MB. Any help is appreciated.
...
6
votes
1answer
145 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 ...
9
votes
2answers
750 views
List<T> implementation for VB6/VBA
Recently I decided VB6's Collection wasn't enough for my needs, so I decided to implement something like C#'s List<T>. ...
5
votes
1answer
170 views
First VBA project
Here's my first VBA project. I have some very limited Python experience, and this is my first VBA project. I'm sure I could have done it a lot more simply, but I just stuck with what I knew, and ...
4
votes
1answer
64 views
How toTighten up and improve a bastardized Excel VBA code
I have the following code. It's purpose is to add up and sort another columns Data. I tweaked it to fulfil my purpose, but I'm sure it can be cleaned up.
The only problem I have with its function is ...
3
votes
2answers
288 views
How can I speed this VBA code up?
I am using VBA to manipulate the global address book in Outlook.
My method takes a contact and returns a complete list of everyone who reports through that person based on the Outlook org structure.
...
2
votes
1answer
328 views
How can I get rid of SELECT…CASE and GOTO in this string formatting helper function?
A while ago I implemented .net's string.Format() method in VB6; it works amazingly well, but I'm sure there has to be a way to make it more efficient.
I'll start ...
3
votes
1answer
115 views
A more readable InStr: StringContains
Consider the following:
If myString = "abc" Or myString = "def" [...] Or myString = "xyz" Then
In C# when myString == "abc" ...
4
votes
2answers
261 views
Sending test emails to a list of email addresses
I've made the following program that takes a list of email addresses from a table in MS Access and sends each a test email.
...
2
votes
1answer
169 views
Dynamic ranges and page-formatting
I've put together the code below, which creates a new sheet in my workbook and applies dynamically-named ranges and page-formatting.
...
4
votes
2answers
124 views
Procedure too long. Cannot execute. How to make it shorter and more effective?
My code is too long and cannot be executed. It simply searches for an empty row in a given destination. If it is empty, it performs certain actions (copying, pasting, etc.). If it is not empty, it ...
3
votes
1answer
2k views
Multiple criterias in a VLOOKUP in Excel VBA
I have made the following custom defined function in Excel. It works somehow like VLOOKUP(), but it takes two criterias. I think the code is a bit of a mess. Does anyone has any comments, suggestions, ...
5
votes
2answers
314 views
Are these good examples of functions that do one thing only or am I getting a little carried away?
I'm reading Robert C. Martin's "Clean Code" and for the past few days I've been fiddling with its function writing guidelines. At first I thought the idea of functions being two/three lines long to be ...
2
votes
2answers
106 views
VBA code needs better way to store recordset data
I have managed to write some pretty terrible code in Visual Basic and I know there has to be a better way. I have made all of these variables to store some data which I eventually reference in the ...
6
votes
1answer
780 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 ...
5
votes
1answer
471 views
My VBS Word macro is really slow, looking for advice!
So, 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 ...
0
votes
3answers
342 views
speed up merging worksheets and using find/replace
Of all the macros that i put into heavy rotation these days, this one is running the slowest. ~4-5 seconds depending on the size of the files. It's not a lot but i'd like to know why code 16x as long ...
3
votes
2answers
350 views
better way to remove from collection in excel vba
In my vba code, I am trying to generate a specific list of cell row positions. Basically how i do that is I first fill a collection with the entire list of row positions pertaining to a specific ...
