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
32 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
74 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
34 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
33 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
32 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
67 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
65 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 ...
5
votes
1answer
52 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
53 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 ...
7
votes
1answer
56 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
81 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 ...
10
votes
2answers
219 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
175 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 ...
8
votes
1answer
206 views

How to avoid the repeat of the code?

Can someone help me please to change the code to an professional one. I try my best to find something similar, but I wasn't successful. I want to avoid the repeat of the code for every single value ...
4
votes
5answers
223 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. ...
5
votes
1answer
134 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 ...
6
votes
2answers
485 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
133 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
62 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
243 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
281 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
100 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
185 views

How can I make this code more efficient?

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. Here is the code: ...
2
votes
1answer
135 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
117 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
308 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
98 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
738 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
376 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
303 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
281 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 ...
5
votes
4answers
845 views

Improve VBA code Excel

Im looking for someone who can improve my code, because at the moment it takes 4 minutes to execute. I'm new to VBA so it's probably bad coding. Normally if you are good in VBA you will understand the ...
1
vote
2answers
5k views

Populate ComboBox

How could I make this code shorter and faster? ...
11
votes
3answers
205 views

How can I improve this 31-bit SuperFastHash implementation in VBA?

I'm implementing a variation of the SuperFastHash in VBA for use in Excel (32-bit version, so no LongLong available) to hash strings. To get around the limitations of signed 32-bit Long values, I'm ...
5
votes
2answers
4k views

Multiple nested If checks in VBA

The below code is real and in use, but I've modified it to simplify the process/make it easier to explain. The purpose of this code is to combine data from multiple data sources. All sources are ...
2
votes
1answer
1k views

Better method to loop through arrays in VBA

I supplied the following code as an answer to this question on SO, but I was wondering if there was a better way to write out all those array loops. (i.e. perhaps using a Collection/Dictionary?) It ...
2
votes
2answers
2k views

Powershell Script for Manipulating Excel Files

I am a newbie at scripting so be easy. I have a directory of xls workbooks with the following naming convention: ...
1
vote
2answers
230 views

VBA Data Conversion (random printout -> CSV file)

I have reviewed the FAQ and I believed it was yes to all 5 q's of "What questions are on-topic for this site?" Basically, the code below is used to perform the operation shown in the picture. I'm ...
7
votes
5answers
370 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 ...
6
votes
3answers
1k views

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

Today, I came across a code example like the following: ...
0
votes
1answer
238 views

what does the below excel-vba codes do [closed]

...