Visual Basic for Applications (VBA) is an event-driven programming language 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.

learn more… | top users | synonyms

4
votes
0answers
12 views

Creating a pseudo Pivot Table / Database using a 4-D array

Why am I not just using a Pivot Table / Database? a) I've never ever used either before. And I don't have time to learn how before this project needs to actually be finished. b) The final output ...
4
votes
1answer
25 views

Defining array contents using multiple statements on one line

I'm writing a sub which is going to determine which type of business is being detailed on a particular line of data. It's going to use column positions to check if the data exists, and then output the ...
6
votes
2answers
34 views

Re-worked Workbook_Open, Creating DateStrings

So, I had a go at re-writing my Workbook_Open event based on The advice from my previous question. Thoughts? ...
8
votes
2answers
73 views

Financial Data From Webqueries in Excel

I'm new (to CR and to programming in general). I wrote my first VBA on Monday. This is my first working project. It Takes a bunch of financial data from a company called Financial Analytics and a ...
4
votes
0answers
25 views

Aggregating data from multiple worksheets into a consistent format

First off, this is the 3rd (and probably last) review of the project in question. You can find the previous question here. The Macro accesses a workbook containing 8 worksheets each with similarly ...
7
votes
1answer
39 views

Standard Methods in VBA

Since I learnt about refactoring, I've been busy creating my own Module of Standard Methods. I feel it's about time they got a review of their own. Open to all aspects of advice but particularly ...
9
votes
0answers
39 views

Win32 File API in VBA

Win32 File API Wrapper Based on a few fundamental frustrations with VBA (namely the lack of ability to work with files larger than 2GB, the lack of encapsulation of the file functions and the lack of ...
7
votes
1answer
56 views

Aggregating 2 lists by matching UniqueIDs

This Macro takes my company's 2 main sources of all-client data (each a data table in a separate worksheet) and aggregates them into a 3rd list, by matching a unique account number (for clients that ...
4
votes
2answers
59 views

Filtering and aggregating data from a multi-Worksheet Workbook

First off, this is version 2 of a previous question about the same macro: Previous Version. Thank you to Raystafarian, RubberDuck and everyone in chat who helped me make it better. The Macro accesses ...
3
votes
2answers
58 views

Loop through column cells and compare value with a Collection

I have a Collection of 166 elements: ...
7
votes
2answers
44 views

Long running time on VBA macro which filters and splits dataset into new workbooks

The VBA macro below uses a worksheet (source_data_worksheet) to filter and split out the records based on about a dozen unique column (D) values in a dataset of about to separate workbooks which are ...
5
votes
1answer
61 views

Matching between files and a list of filenames at scale

Given a folder typically containing ~250,000 small (20-100kb) files in HTML format (they open as single-sheet workbooks in Excel) and a list of ~ 1 million filenames, I need to open all the files that ...
2
votes
1answer
61 views

Finding the position of a string in a dimension of an array

Given an array and a string and bounds within which to search, I need to find the position of the string within those bounds. What I want to optimise: If possible, I want to re-design the function ...
5
votes
1answer
171 views

Automating the aggregation and filtering of an 8-worksheet spreadsheet

Every piece of business that gets written (E.G. an adviser's client puts an additional £10k payment into a pension plan) goes in a spreadsheet called the SubSheet. It's split into 8 worksheets for ...
5
votes
1answer
38 views

Refer to other cells besides the one in the Cells.Find

I have the following that loops through a range. When the initial value is found on the second sheet it goes to the cell to be able to compare some date values on the same row. Once the comparison ...
5
votes
2answers
43 views

Excel SLOOKUP UDF

I created an Excel AddIn that has a function called SLOOKUP which can replace INDEX MATCH combinations in an easy and smart way. ...
19
votes
3answers
181 views

Racetrack in… VBA?

So, I took a look at the August Challenge: The top-voted answer is Racetrack1: "In the game of Racetrack2, cars race around a track bounded by two concentric closed loops drawn on a ...
6
votes
1answer
43 views

Hide and un-hide rows then sort on Worksheet_Activate event

We used this VBA code mainly to hide blank rows and unhide non-blank rows. After that, the second code sorts the rows by a defined column value once the worksheet activates. This process takes too ...
2
votes
2answers
64 views

Copying and pasting from MS Project to MS Excel

This copies data from a project into Excel to be calculated into a final report. The code runs pretty slowly at this point and I would like it to be a little bit faster. Are there any blatant coding ...
2
votes
2answers
22 views

Pick out excel cells from a long horizontal list based on an adjacent numerical value

I've already written this macro that does exactly what I want it to do. However, it's quite large, and I may wish to expand on it in future. Basically, the purpose is to copy and paste items from a ...
5
votes
2answers
73 views

Downloading stock information from Yahoo! Finance

The program downloads stock information from Yahoo! Finance and displays it in the spreadsheet. On my Mac the program takes 10 minutes to get data for approximately 4000 stocks and on the PC it takes ...
7
votes
5answers
372 views

Copying and comparing two sheets before deleting duplicates

I am running this code as a macro in Excel, which copies two columns from sheet 1 and pastes them into sheet two. It then compares the first column to a column in sheet two before deleting any ...
0
votes
1answer
68 views

Transferring information between sheets based on column headings

This function conditionally copies and pastes information from one sheet to another based on the column headings to standardize data before exported to SQL database. I am not an expert in VBA and ...
6
votes
1answer
68 views

Porting ProcMonDebugOutput from C# to VBA

This code has one public method. ProcMonDebugOutput(). It will sent a string of text to Process Monitor that process monitor will display. I'm basically ported ...
8
votes
1answer
56 views

Unit test code for Antlr Grammar

I'm working on a grammar for the Visual Basic for Applications (VBA) programming language. I've discovered a way to make assertions about how a parse tree should be generated by using the Antlr ...
5
votes
2answers
64 views

Adding all IDs associated with a Fax to one Row

I found a custom function called UniqueItems() to use and then developed my own RemoveDups() and ...
5
votes
2answers
87 views

Copying data entry values in to a separate WorkFile

The code is working but slow. Is there a way to speed it up or make it more compact but still easy to read? There are 35 different jobs to be done. They are made in a worksheet with merged cells to ...
2
votes
1answer
73 views

Streamlining function that gets the length of each value

The code works well but before I added sections (13) and (14), it ran in 6 minutes and now runs in 16 minutes. If there is a way to streamline this to cut down the runtime, that would be ...
4
votes
1answer
105 views

Excel macro to move data across workbooks using an ADO connection

I am writing a macro to move data from a CSV to an excel template. Currently, I have the code set to search for a keyword in column A of the CSV, and extract data from specified columns in the ...
3
votes
2answers
218 views

Converting CSV files to Excel workbooks

My converter converts all CSV files in the subfolders of folders 1, 2 & 3 into Excel workbooks. As of now, I am converting using codes for each folders. I previously tried to combine those into ...
3
votes
1answer
58 views

VBA macro to move data across workbooks

I'm new to VBA and am trying to write a macro to move data from an exported file to an excel template. The code below works, however I have to add many more ranges of data and am worried it will be ...
6
votes
3answers
142 views

Extracting data from CSV file on the internet

This is my code I made that extracts data from a .csv file stored on the web. My sheet called 'New Data' stores this data for 2 days. I store 2 days worth of data so that when I run it next time, it ...
3
votes
3answers
187 views

Copying a range from one file to another

Since it has too many loop, loop inside loop, this works very slowly. How can we increase the speed of this program? I am trying to copy a range from one file to another file of same name in different ...
5
votes
3answers
557 views

Try catch statement in VBA using the standard VBA error handling statements

The code below is pretty self explanatory: just copy and paste it all into a module and run it, it provides a few use cases and many explanatory comments in the text. (It works but I'm interested to ...
5
votes
1answer
137 views

Retrieving ListBox.column values and store into objects

I know there must be a better way to perform the above sub than using multiple If statements. I was thinking of storing the ...
7
votes
3answers
194 views

Colorizing Excel cells with a date range filter

In a table there is a number of column (chambre) and a number of row (alveole). If these matches in a list in an other sheet then it checks if it the date is between two dates on that second sheets. ...
4
votes
2answers
221 views

Calculating how long sales reps are idle

I wrote a procedure to calculate the number of months elapsed since a sales agent' last sale. It works but runs in over 10 minutes... for 20 rows. ...
5
votes
2answers
111 views

Select and Paste Rows in a Different Location

I am working with the output from a chemistry instrument, in .xls format. The output contains up to seven blocks of data corresponding to different types of samples e.g. controls, unknowns, ...
4
votes
3answers
46 views

Copying data from an Excel sheet to Word

This code copies a specific range and a chart from an Excel sheet to Word. This code works fine but it is very slow. I have used delays(Wait) to work properly while ...
5
votes
2answers
191 views

Intense worksheet manipulations: what price did I pay for performance optimization?

The code bellow was refactored for performance improvements for another user on this site. Functionality, high level: Sheet1 - CodeName aIndex: used as the main reference to the structure of the ...
5
votes
3answers
117 views

Copying columns and formats on condition

I have made significant changes in the code and now it takes about 20~40 seconds to process each worksheet. This will conditionally copy column based on the column headers in sheet2 that match to the ...
4
votes
3answers
177 views

Sorting and color-coding based on variables

I have the following three macros all running in the same module. Each one is attached to its own button (3 buttons total). All three buttons take an average of 6-12 mins to run. As I am working on ...
5
votes
2answers
115 views

Searching a huge number of strings from Excel to a large number of Text files

I have written this code to get the input string from the spreadsheet and search the string across the text files and state whether or not it is found in the Excel sheet. Scenario: Excel (sheet1): ...
3
votes
3answers
112 views

Translation function - one word set to another

I have this Excel VBA function that takes three parameters. It is essentially a translation mechanism with the following parameters: A sentence which has to be translated. A range of words to look ...
7
votes
4answers
156 views

Looks at order data and arranges it to focus on order locations

I have a sheet that order a bunch of information on individual orders. Order numbers are in column A, other information is in columns B-P, and then product numbers are the headers for columns Q-DL. ...
5
votes
1answer
83 views

Open files, copy area under header, print to mastersheet

This loops through folder to open files and get important info from the columns of names "HOLDER" and "CUTTING TOOL" and printing all the info to one excel document, masterfile. It utilizes a ...
3
votes
1answer
58 views

Parameterized Query in Access SQL

I am a beginner in Access SQL. I am in need of writing a parameter query for Access SQL and found a few tutorials and links for that but that wouldn't satisfy my requirement. DB: Access DB This ...
3
votes
3answers
177 views

Clearing Pictures Code

The following code removes all pictures that are placed into Column A. When running the code however I have noticed it runs fairly slow. This will be attached to a command button that will be located ...
6
votes
3answers
151 views

Conditionally copy columns

I have this Excel/VBA code and here is what it does: I have 3 sheets, where first sheet has two columns that will be used (A,C). 2nd Sheet is just the raw data file that will be imported from ...
7
votes
1answer
134 views

Finding and pasting images into a specific cell

The below macro finds and pastes images into column A. While the macro works, it starts to slow down when running 500+ images. I am not too familiar with the VBA language, does anyone have any ...