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

0
votes
0answers
10 views

Auto-upload tool (VBA, Excel)

i created an automatic upload tool, working great (most of the time). As i want to improve the code i have several questions: 1)Is it possible to make the iexplorer progress some kind of smoother? I ...
3
votes
0answers
9 views

Using VBA Copying from one ,master sheet to other workbooks based on the file names provided in master book

Pretty new to this hence taking it as challenge. I have a master sheet in which Column A have all the file names( complete file name with .xls extensions). I need to copy data of column b in this ...
7
votes
0answers
26 views

Interpolating data of multiple trains

I have written a piece of VBA code to essentially replace a complex formula that I was using in the Excel cells. I have the data on Sheet2, which describes multiple train journey's along the same ...
1
vote
1answer
19 views

CLS_Compound_Predicate

I have a CLS_Comparison_Predicate which will take an input value, perform a logical comparison and return a Boolean. CLS_Compound_Predicate instead takes lists of ...
-1
votes
0answers
18 views

Excel VBA - Copy multiple worksheets to other existing worksheets [on hold]

I have 10 subsidiary company P&Ls that I report on a weekly basis. At the end of each week I want to rollover the file into a new week by copy-pasting each of the P&Ls into already existing ...
4
votes
2answers
77 views

Copy, Paste and Format 2.0

This is a follow up with revised code, see the original question and subsequent answer in the following link (Copy, Paste And Format) This is a full version of all three "Macros" or subs within the ...
9
votes
1answer
529 views

Copy, Paste And Format

I am currently working with this code to automate some tasks for senior staff members that are not very adept in Excel. Wondering if VBA is simply not a very quick code or if my code is clunky and ...
7
votes
1answer
55 views

Class: 2D Variant Array

Note: Yes. It's big. I'm not expecting commensurately long/detailed answers (though if anyone wants to write one, you'll definitely be receiving a substantial bounty). This class is going to be ...
4
votes
2answers
28 views

Re-arranging data on a worksheet based on contents of Column A

The following Macro sorts through a worksheet, copying and removing lines based on what appears in column A. If ("A" & i) is not a number, it triggers and moves the data to the appropriate place. ...
1
vote
0answers
21 views

An [InternalCopyArray] by any other name

So I'm building a 2D Array class. I want to have CopyArray and RemoveIndexes functions. However, in both cases, the method ...
3
votes
1answer
21 views

Code to copy, paste and summarize a data set

The code currently works as it should and seems to work dynamically as I need it to but I am pretty new to Excel so I think there is probably a better way to do some of the things I have set up. I ...
5
votes
1answer
31 views

Rearrange data without using Cut and Insert

I'm fairly new to VBA, and this was basically a brute-force solution to a problem I was encountering. I wanted to take data that appeared in two columns, and pull it together into one. The current ...
7
votes
1answer
239 views

Logical Predicate Class

For a while now I've been playing around with various ways of passing a logical predicate E.G. >= someValue as an argument to a function. (see: If ...
2
votes
1answer
28 views

Web scraping with VBA

I have this code that fetches rates from a website called X-Rates, and outputs to excel the monthly averages of a chosen country. The code runs quite fast, but I still think there's improvements to ...
3
votes
2answers
96 views

Copying lists into columns of a table

Edit: I found a potential solution. Changing all of the Dim as Long to Dim as Integer allows the scripts to run smoother. ...
3
votes
4answers
362 views

Copying orders and prices into another Excel sheet

I would like to know if the below VBA can be streamlined to process faster, since it takes on average 9hrs to complete a sheet (800 000 lines), and I have quite a few to get through. Running on 3 ...
3
votes
0answers
25 views

Creating two dictionaries to lookup values into an array

In my attempt to help improve Using Array to store calculations in VBA, I figured a good way to do it would be to create two dictionaries of values to lookup. So this was my attempt at creating two ...
6
votes
2answers
187 views

Fast(er) web scraping with VBA

I have a code that fetches rates from a website called X-Rates, and outputs to excel the monthly averages of a chosen country. The code runs quite fast, but I still think I could improve the code a ...
3
votes
2answers
38 views

Transfer the cell formatting in a legend to a dataset in another sheet

On Sheet 1 is a legend that contains unique values in Column A every ten rows. To the right of each value in the next five columns over are a series of color filled cells that extend down ten rows to ...
3
votes
2answers
57 views

Using Array to store calculations in VBA

I have the following VBA code, which works perfectly well to calculate "q" However, the code is very slow and that is due to the large number of q's being calculated (roughly 7.2m q's are being ...
1
vote
2answers
50 views

Re-arranging an matrix of numbers to a numeric list

If I have an array of numbers like this: 1 3 4 6 7 1 2 2 4 5 9 5 7 1 2 3 5 Is there a quick way to take all of the unique numbers and arrange them ...
3
votes
2answers
53 views

Assigning entire column of data to specific column of an array in VBA

The below code I have written is to allow me to Sum the results data for every year from 1 to 1000 over a range of 5000 locations. although a 1000 years seems extreme, for the application I am using ...
4
votes
2answers
47 views

Re-arranging a list into columns

In short, what this script aims to do is take the following data: 1 Alpha Beta Delta 2 Beta Echo Foxtrot Kilo 3 Alpha Kilo 4 Beta Echo Kilo Zulu And re-arrange ...
0
votes
2answers
28 views

Cut specific rows and paste to new workbook faster

I'm quite new to trying to write my own macros and I've been able to piece this one together, but it takes quite some time to run. Here is the macro for one value (I have to create workbooks for ~20 ...
1
vote
2answers
35 views

Return the best factors of a whole number

I've just finished writing my code to return the prime factors of a number and I feel the GetBestFactors function could be improved. The ...
2
votes
1answer
38 views

Excel VBA to create a visual timeline of events using an XY scatter chart

I have details of key decisions made by a business in a worksheet "Decision Record." My data is laid out as follows: I have written some code to create a timeline of these decisions using an XY ...
3
votes
3answers
63 views

Searching across text files

I have 7 source files that I am searching and cross-referencing. I have tried a few different methods inside Excel (cell-by-cell iteration, dictionary), and found select statements as recordsets to be ...
4
votes
0answers
78 views

Plotting charts for a large data set

I have created an automated macro which takes vehicle crash data from a .csv file and automatically creates a pivot table and plots and compares it to the previous year. The code is approximately 1400 ...
6
votes
2answers
50 views

End of day receipt emailing process

The following functions/routines are an automation of an end of day receipt emailing process that formerly would take up to an hour, but now takes less than a minute. Things to note: I changed the ...
6
votes
2answers
60 views

Comparing vehicle crash data the previous year

I have created an automated macro which takes vehicle crash data from a .csv file and automatically creates a pivot table and charts and compares it to the previous year. The code is approximately ...
14
votes
3answers
241 views

Modularise Project and Deduplicate Code By Loading External Modules On Startup

Starting point I kind-of inherited a VBA project at work that consists of a bunch of Word document templates that provide users with the tools they need to create documents according to company ...
6
votes
2answers
56 views

Change list separator, parse file, restore list separator to original value

Following up on @Vogel612's friendly advice from the vba-rubberducking chat room, I am posting my working code for open review. Thanks also to @Mat'sMug for the ...
4
votes
2answers
55 views

Pick a folder and consolidate workbooks

I'm trying to create a simple way for current & future (non VBA literate) employees to accomplish some of the tasks I've struggled with. The first being Parsing cells containing Line Feed ...
7
votes
2answers
74 views

Parsing 4 Million Filenames

This is the next step in my project to query Companies House records. The first step, retrieving and validating the company numbers to be targeted, was covered here and I will include that code at the ...
4
votes
1answer
46 views

Validating company registration numbers

N.B. The next stage of this project, which covers parsing filenames and checking numbers against the list produced in this question, is covered here. I have a list of company numbers (UK companies ...
4
votes
2answers
58 views

Parsing cells containing Line Feed Characters

Link to sanitized xls on dropbox if test data is needed Essentially the reports I work with aren't bad - The issue is the way it exports to excel - With the problem being that these cells are ...
3
votes
2answers
366 views

Count rows of a table

I am trying to count the rows of two tables generated by SQL connections in Excel VBA. My plan was to use ListObject.DataBodyRange.Rows.Count however, this errors ...
2
votes
2answers
40 views

Manage InputBox method reference entry

Exploring the VBA Application.InputBox method I've realized that catching user cancellation is tricky when type is reference, so return value is a range object. ...
7
votes
3answers
127 views

Turn logged data into fancy plot

I'm logging data from an embedded platform over UART. This gets saved to a log file and I want to process the saved log with Excel. I have no idea whether what I did was even remotely a good ...
9
votes
2answers
252 views

Extracting Country Names from Cell Values

This fairly clunky looking VBA script successfully pulls names of countries from an Array, but could it be made shorter? Mass of text from Column C onward in each row may contain the names of ...
5
votes
3answers
71 views

Extracting information from spreadsheet into 12 tables (one per month)

I have a file with more than 100k rows, but the structure is simple: ...
3
votes
1answer
45 views

Handling name variants

Part of my standard modules deal with my company's employees and linking them to/from various data sets. The following are a series of enums/functions to handle peoples' names, namely an ...
5
votes
2answers
125 views

VBA Copy Cells Stable

I'm very new to VBA and have put together a macro that currently does exactly what I want: copy cells from one worksheet to another, clear the cells copied, refresh my pivot tables and save the file. ...
4
votes
2answers
203 views

Converting all cells to text

Microsoft Excel cells basically have two values: the formatted value that is shown to the user, and the actual value of the cell. These two values can be extremely different when the cell has certain ...
5
votes
2answers
61 views

Replacing accented letters with regular letters in a spreadsheet

I am using the following VBA code to replace accented letters with regular letters in a spreadsheet. This is necessary because these spreadsheets have to be uploaded to an import tool that does not ...
3
votes
2answers
57 views

Excel VBA highlighting macro

I have this Excel macro I created to highlight all instances of a number if at least one instance is already highlighted before running the macro. ...
6
votes
1answer
142 views

Modularised Document Construction

The goal: A set of configurable options, combined with a standardised data source, to produce a standardised Report. This question: One module of said document, with accompanying code. Input: ...
4
votes
3answers
37 views

Handling errors with minimal repetition when using Find function

I have a function that essentially searches out a value in a table by looking through the column headers and row headers to locate a data point. The function takes the sheet name, the column header ...
4
votes
3answers
230 views

Deleting rows based on a date

I am deleting rows based on a date. I am loading the entire sheet into an array and doing the evaluation and building a string containing the rows I want to delete. ...
10
votes
2answers
241 views

Conditionally deleting rows in a worksheet

I have used loops to remove lines that are doing various things, but all are deleting rows where certain criteria is met. Some are deleting less than certain numbers, deleting blanks, removing ...