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

6
votes
0answers
35 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 ...
6
votes
2answers
57 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 ...
-1
votes
0answers
10 views

From VBA to R: what can I use in R that does the same as ReDim in VBA? [closed]

I am currently working with the explicit finite difference method and I have a code for this method. The code is written in VBA and now our teacher wants us to write the code so that we can use it in ...
5
votes
1answer
62 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
28 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
53 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
29 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
88 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
50 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
45 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
81 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
102 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
101 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
151 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
71 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
39 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
69 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
96 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 ...
15
votes
2answers
283 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
42 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
96 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
77 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
206 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 ...
2
votes
1answer
24 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 ...
1
vote
0answers
45 views

Setting “Find(What:=” to a column in a different .xls file [closed]

I currently have code that opens up an empty workbook and then a master file with 5000+ rows of info. A pop up then appears and asks for a ID. I type in my 6-digit ID and the macro goes through column ...
3
votes
1answer
68 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
49 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
170 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
214 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
208 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
89 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
123 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
95 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 ...
10
votes
2answers
105 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
103 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
67 views

Join two collections by date with no duplicate

Data I have 4 Collection objects of the following kind: ...
5
votes
3answers
267 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
391 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
45 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
322 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 ...
5
votes
1answer
77 views

Conditionally copying cells from other worksheets

I wrote super redundant VBA code and it works and solve my problem. I am trying to simplify it but don't know where to start due to my limited knowledge in VBA. Basically, this code works as a ...
7
votes
1answer
118 views

Access function to return “number of working days until date” for form

I need a function I can put on a form which does: Calculates number of working days until a given date Excludes weekends Excludes any dates in a "holiday" table This is replacing something which ...
6
votes
2answers
79 views

Generating fiscal calendar dates

I need to generate a T-SQL script for loading data into this table: ...
6
votes
1answer
114 views

VBA Code Profiling

I have wanted a way to do code profiling in VBA for quite some time. It becomes very complicated to figure out what methods are actually being executed for how long and how often in complex Access ...
12
votes
3answers
220 views

Creating an object oriented model in VBA using COM and ADODB from 2 depended SQL tables

The story... A bit of background info and how is the database designed... Please notice you don't really have to rebuild the tables in SQL but I shared an SQL Fiddle just in case and screenshots1 of ...
5
votes
2answers
301 views

VBA ClickBot featuring AJAX waiting and Element searching

If you work with a database that you only have access through a web interface, this one is for you. You probably don't have access to any programming languages other than VBA through MS Office, which ...
6
votes
2answers
119 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 ...
6
votes
1answer
416 views

Excel VBA binary search class module [closed]

I have a long list of sorted strings in a Workbook, and I have to find a lot of values in it. I was hoping to build a binary search Class Module that will find them for me faster than Excel's built-in ...