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

4
votes
2answers
157 views

Calculate grades based on pass/fail percentage

Our school grading scale is from 1..10 with one decimal. If you do nothing you still get a grade of 1.0. A passing grade equals 5.5. A 'cesuur' percentage defines at what percentage of correct answers ...
1
vote
1answer
32 views

Inserting row into named range code

I am writing a macro for an inventory management document. The point of the User Form is to allow the user to add a new item to each of 5 sheets (Daily Sales, Total Inventory, Deliveries, Income ...
3
votes
2answers
67 views

DAO recordset effiency

I have a form that consists of about 60 fields that all update when choosing a different customer from a drop down. I feel like my current way is not the best way as it takes about 30 seconds to ...
6
votes
2answers
278 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 ...
2
votes
1answer
48 views

Using a nonsense string to check if a call has completed, rather than timeout?

My question is whether or not this is a good practice, and/or if there is a more efficient way to do it. I have to access this Solidworks interop object and wait for it to return some value. The ...
10
votes
2answers
340 views

Handling try catch in VB6

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 ...
4
votes
1answer
31 views

SQL + VBA Populating Data in Cell Range

I currently have a code that is connecting to SQL database in VBA. The data is populating correctly. However, I was wondering if there is a way to condense the code below. I have 4 different columns ...
3
votes
2answers
50 views

From New Worksheet, AutoFill Dates, Delete Extra Columns, Save As Month and Year

I have created an inventory management sheet for a Lodge in Africa. The general conditions of the document are that it has to be easy to use, because the people who are using it are not very computer ...
4
votes
1answer
48 views

Improving performance of VBA data validation script in Access with large data size

I work in IT compliance and one of my main duties is ensuring the integrity of employee network accounts. Partial VBA script as currently used is shown below: ...
5
votes
1answer
120 views

VBA Text import and sort Excel

I have routine tasks which require pulling data from an obsolete SPC package and putting it into Excel. The SPC software will only export into a text file. I have put pieced together a script to ...
12
votes
2answers
172 views

A reusable ProgressIndicator

There was a Tweet from @ExcelEasy earlier this week, that linked to this article, to which I replied with a little challenge: @ExcelEasy well done! #Challenge: make a more #OOP one without using ...
7
votes
2answers
91 views

VBA Text Array - Scan two columns rather than one

I have some code which is designed to scan columns F and G for occurrences of words found in an array, the array containing text ...
5
votes
1answer
110 views

Working with group of checkboxes in MS Excel

I have the following checkbox as depicted by the image below: The checkboxes are in 3 groups. Confidentiality, Integrity ad Availability. When the first in is clicked, the Confidentiality turns Red ...
4
votes
1answer
37 views

Speeding up Access query with a calculated field calling a VBA function

At work I have a Microsoft Access database of employees with fields such as employee's manager, employees email, and employee ID. My duties include updating this database and validating entries ...
2
votes
1answer
61 views

Select Case w/Public Functions

I am looking to enhance my VBA skills and efficiency with the code below. It currently executes at about 20 minutes. I just recently learned how to use public functions but this project is very ...
2
votes
1answer
35 views

Comparing cells of two Worksheets

With the following Code I try to compare the content of two Worksheets. The first column contains a key that is unique and the following columns contain data that I want to compare (My data sets ...
5
votes
2answers
117 views

Look for keywords and copy to another sheet

I currently have a working code that does exactly what I want it to do, loops through a particular part of an excel document looking for certain keywords, then pasting those keywords into a separate ...
2
votes
1answer
59 views

Loop through folder names on a server directory

I have the following code to loop through a directory of folders and print all folders names with their paths in a worksheet. This is a follow up question to this one: Excel VBA - Get Folder Names ...
2
votes
2answers
54 views

Excel .find Macro to write data to archive

I would like to speed up this piece of code. The macro gets a search variable from the worksheet "RAW_DATA_ARCHIVE" and then it should find it in the worksheet ...
3
votes
2answers
124 views

Excel VBA - Get Folder Names

I need to get folder names with the path for which I need to search the directory in a server with about 6000 folders. I have the following snippet of code to run through the folder and get the folder ...
4
votes
2answers
108 views

Extracting and analyzing data from SAP

I developed a script for the company I work for in order to extract data from SAP (CJ74) and analyze the data. One particular issue I am finding is that for any data sets which have over 1000 lines, ...
6
votes
3answers
238 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
109 views

Copy data to another worksheet

I have the following but it takes long time to run, it is a simple wherein the user select a file and data from Sheet1 is copied to another workbook. ...
5
votes
4answers
158 views

Find function for strings in cells

In column A, I have many strings ~ 32000 cells each containing unique string values. I then have columns C to as many as 30/40 columns which all contain approximately 400 rows of text. Check column ...
4
votes
2answers
79 views

Text files: Copy, Rename, Append/Merge together

I wrote 3 subroutines related to batch data processing, they will be used together. A bit of background, I wrote this for my admin colleagues who do not write code. An application dumps daily ...
5
votes
1answer
40 views

Update on Weave Merging n lists into single list

This is a follow up to my previous post from 7 months ago. I changed up the algorithm a little. Instead of inserting items into a new list, each item's final place is calculated up front. Sort of like ...
8
votes
1answer
81 views

Growing my own Tree (Structure)

Summary VB6 doesn't have a great selection of data structures to work with, so again I find myself creating my own. I have a need to dynamically generate a directory structure on the file system. The ...
4
votes
2answers
112 views

Counting and Deleting Duplicate Data

Can anyone help me optimize the current VBA code below? It is taking 20 seconds currently to collect,store, check and validate 1000 data's. This program takes specific values from multiple sheets and ...
11
votes
2answers
99 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 ...
16
votes
2answers
286 views

Randomizing Civilization 5 team choice

This macro is made to randomly draw specific number of civilization choices for a specific number of Civ V players. It is based on a list of 43 possibilities and the ...
4
votes
1answer
44 views

Key on a Header and Offset

I have a section of code that I've been attempting to clean up and hopefully speed up. The section of code where I pass values in order to generate a key and update a table on a different sheet I ...
5
votes
2answers
100 views

Replacing a For Each loop with something faster

I'm a very inexperienced VBA user, I've just created my first "For Each" loop but it's very slow. I'm happy that it works but eager to know if there is a alternative method that is quicker. Any ...
6
votes
1answer
79 views

Quicker way to go through 550 variables with very similar code

I'm hoping to figure out a faster way to write this before I resign myself to copying and pasting 500 times. I have code here that needs to do tons of vlookup matches. The only thing that changes ...
5
votes
2answers
255 views

Finding matching PDF files in different folders and merging the two files into one

I have code which currently loops through one folder, finds two matching pdf-filenames and merges them into 1 pdf-file always in the same order. File 1 then File 2. My code is slow, maybe because ...
3
votes
1answer
26 views

Average interval between dates with random blanks updated

After getting some help at Average interval between dates with random blanks, I rewrote most of the code. Here is the updated code: I have a spreadsheet with order date data: I need to find the ...
3
votes
1answer
93 views

Representing Objects as strings in VBA

I am working an a more meaningful way to print objects in VBA. The desired result should look something like this. ...
4
votes
1answer
53 views

Average interval between dates with random blanks

I have a spreadsheet with order date data: I need to find the average interval in days between each order date. I have to both find a way to get past the blank cells in the row, and also take into ...
2
votes
1answer
191 views

Calling Stored Procedures with Lots of Parameters

I have this piece of code for running a Stored Procedure, and I was wondering if there is a way of cutting code like: ...
8
votes
2answers
216 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 ...
5
votes
1answer
241 views

Copy Cell on Worksheet_Change event if some criteria is met

This code is working fine, but it looks awful (yes, I am a rookie on this thing). How can I improve it to look better? ...
5
votes
4answers
94 views

Modifying excel code to speed up the process

I'm currently trying to create a macro, to edit a document that is around 280k rows and 12 columns. For the first steps I found a macro to delete the rows I don't want based on certain criteria, but ...
4
votes
2answers
132 views

Copying cells from one sheet to another

I'm new to writing VBA and having spent most of the day writing some code, I have finally got it working but it looks horrible. I'm hoping that someone can help me tidy it up. The code simply copies ...
4
votes
1answer
103 views

Converting to Roman Numeral with Recursive Algorithm

Summary I'm getting ready to dive back into a "more proper" project, so I wanted to take a moment to get my TDD hat on before doing so. I decided to tackle this Roman Numeral Kata for practice. The ...
11
votes
3answers
148 views

Karate Chop Kata

I had some time to kill today, and I found the Karate Chop Kata. Specification: Write a binary chop method that takes an integer search target and a sorted array of integers. It should ...
4
votes
2answers
109 views

Retrieving stock prices

It takes around 5-8 seconds for me to retrieve a previously-closed stock price and a dividend rate from US Yahoo! Finance. If I wanted to retrieve 10+ stock prices, it would take me more than a minute ...
5
votes
2answers
69 views

Join two collections by date with no duplicate

Data I have 4 Collection objects of the following kind: ...
5
votes
3answers
343 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 ...
7
votes
1answer
443 views

Improving performance of a VBA code to read contents of many .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 ...
6
votes
2answers
51 views

Cascading Changes to Future Entries in a Schedule

I've been working on a scheduling application and I have the middle tier completed at this point. It's not changed in a few days, so I feel it's ready for review. I have just this one routine that ...
6
votes
1answer
381 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 ...