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 ...
4
votes
0answers
23 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
55 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? [on hold]
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
61 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 ...
0
votes
0answers
44 views
Excel 2010->2013 VBA Slow [closed]
We currently moved our whole office to Office 2013 and are running into slow issues with our VBA script in excel. It seems to lag the most in the for loops. Is there anything we can do to optimize ...
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
87 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
101 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
236 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
266 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
390 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
300 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 ...
