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

3
votes
2answers
41 views

List(Of ) like operation in macro

NOTE: I previously asked this question in Stack Overflow and got pointed here. I have a macro that I am trying to get running a little more quickly. The intent of the macro is to merge multiple files ...
-3
votes
0answers
9 views

Converting Access 2010 fields for VBA [on hold]

Option Compare Database Option Explicit Sub AuditChanges([VIN Number] As String) On Error GoTo AuditChanges_Err Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim ctl As ...
4
votes
3answers
57 views

Dealing Poker Hands

I thought I'd give a shot at creating my own version of dealing 5-card hands to n players in VBA, printing them to columns and coloring hearts and diamonds red. I felt I might have been a little ...
8
votes
4answers
85 views

Converting from .xlsx to .txt

I have code that converts each sheet of a spreadsheet into a .txt file. It works well, however, given the big number of exports (about 90 .txt files), I'd like to seek advice on how to speed it up. <...
-2
votes
0answers
22 views

Consolidation Macro for Excel [on hold]

Let me rephrase my question. I'm looking for feedback on a specific working piece of code from my project in the following areas - Best practices and design pattern usage, Performance, Correctness in ...
3
votes
3answers
54 views

Error handling for cells.find

Revised and truncated version of my last question. I updated how I handle the error if a cost center is not found. I originally asked expecting improvements on the matching system so thoughts on that ...
0
votes
0answers
18 views

Automatically pull in data from one workbook into a template workbook and update the formulas [duplicate]

I have two workbooks: one being a template file with saved formulas that extend to the 30,000 row and the other workbook that contains the data that is input into the template file. Due to the fact ...
6
votes
3answers
74 views

Loop through a unique combinations driven by choices

I am trying to optimize my code and make it as fast as it can be, I have researched as much as I can and am now asking the community for any tips because there are definitely people more experienced ...
5
votes
3answers
236 views

Transferring data for a given month from one sheet to another

I have dollar amounts sorted by month that I want to transfer from sheet entry to sheet actuals. I only want to do it for a ...
10
votes
3answers
702 views

Macro that does some kind of duplicate check

This macro pulls in two workbooks, one being a template with saved formulas already, and the other containing data with thousands of rows...I need to increase the speed because the process takes more ...
0
votes
0answers
21 views

Defining long, multi-line string-literals in VBA [closed]

I'm writing a helper in C# (for inclusion in Rubberduck) that takes text from the user's Clipboard and reformats it for insertion into a VBA module as a string-literal. The goal is to allow the ...
-2
votes
0answers
18 views

Retrieving items from array returning same item [closed]

Coming from JS, I've realized the methods for working with arrays in VBA are very minimal. Because of this, I've created this mess of code to find the index of certain items within 2D arrays. The ...
4
votes
1answer
104 views

Clearing filters, copying a range, and then pasting a formula down a row

Novice coder here. I have the following code to perform a few actions like clearing filters, copying a range, and then pasting a formula down a row till the last line. It works perfectly, but it ...
10
votes
3answers
94 views

Userform for getting data from data sheet into a table

I am making a userform that grabs data from a data sheet and puts it into a table: Grabs the data based on what the user wants (Brand -> Items for brand) Allows multiple items to be added Displays ...
10
votes
4answers
564 views

Copying values from one sheet to another

I am trying to copy values from one sheet to another using loop with two conditions and it is very slow. Removing conditions from code doesn't have any effect on the speed of the execution. I already ...
7
votes
3answers
217 views

Summarize raw data in new sheet, 2.0

Thanks to zak for some great advice on this previous question. This is a follow up question: I have an Excel workbook with two sheets: "Raw data" and "Summary". In the raw data sheet there are ...
2
votes
1answer
37 views

Summarize raw data in new sheet

Note: This question has a follow up question here. I have an Excel workbook with two sheets: "Raw data" and "Summary". In the raw data sheet there are several blocks with data structured like the ...
9
votes
4answers
1k views

Create a list of random numbers with sum = n

I want to create a list of random integers in the range [0, 9], that sums up to 100 in Excel, using VBA. The list should be printed in a single column. The routine I've written is as follows: ...
2
votes
0answers
31 views

Mailmerge from Excel to premade template in Word

I've written the following code to take data from the Excel file "Data" and use it for a mailmerge in a prepare Word template named "Template." I'm new to using VBA in word or Access, so any feedback ...
9
votes
5answers
821 views

Macro code that removes excess rows

I'm a business student who just started to learn VBA. I am trying to write a macro for a project but only have minimal experience actually stepping into the code. The macro I have written is to ...
5
votes
1answer
45 views

Compare and find duplicates in 2 corresponding columns in 2 sheets

I want to compare (500) and find duplicate daily records within 2 sheets, and copy the unmatched row to another sheet, copy the match from another to 3rd sheet, and delete the matched records from ...
3
votes
2answers
44 views

Consolidate used student hours into master sheet for export

I've developed a spreadsheet that has evolved over several years to help keep track of the number of student contact hours that have been used to help flag up when students have run out / are running ...
6
votes
1answer
55 views

Excel VBA to parse JSON out of Google Maps API

Some code that I wrote and broke into 5 separate questions on stack overflow. Some guy wanted to query Google Maps receive some JSON and then for each result found within that JSON do another query. ...
2
votes
2answers
51 views

Importing data into Excel

Is there an easier way of importing data into an Excel array or other data structure? I've tried researching collections but I have found the documentation hard to comprehend. MSDN The code I have ...
6
votes
1answer
194 views

Print worksheets depending on checkbox. Slower than before

I have an userform that has check boxes where user can decide which worksheets to print. Code in userform: ...
4
votes
2answers
57 views

Importing JPG, JPEG, and PNG images from a folder to an Excel worksheet

As I am a novice, I was keen to solve the task, so now I want to optimize the code. ...
4
votes
1answer
34 views

Auto-upload tool

I created an automatic upload tool, which is working great (most of the time). As I want to improve the code I have several questions: Is it possible to make the iexplorer process smoother? I don't ...
4
votes
1answer
44 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
1answer
58 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
29 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 ...
4
votes
2answers
83 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 ...
10
votes
1answer
551 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
72 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 used ...
4
votes
2answers
34 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
27 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
22 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
34 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
245 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
39 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
103 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. ...
4
votes
4answers
371 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
42 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 ...
8
votes
2answers
222 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
39 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
71 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 ...
2
votes
2answers
59 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
58 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
49 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 ...
1
vote
2answers
32 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 ...