Questions tagged [vba]

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 and over 200 non-Office hosts.

1
vote
0answers
10 views

Excel - Visual Basic For Applications - Mastermind Game

For those who are unfamiliar with the game: https://en.wikipedia.org/wiki/Mastermind_(board_game) A link to the workbook: https://github.com/Evanml2030/Excel-Mastermind Had trouble getting the ...
2
votes
0answers
47 views

Update a table based on other table

I created this simple macro that works fine for its purpose: allow the user to create a list of updates that need to be made based on groups in a target table (to basically do mass updates). However,...
-1
votes
0answers
34 views

How to skip blank columns in copy paste function? [on hold]

I am incredibly new to VBA and have inefficiently coded a macro to save a lot of tedious effort but would like to make it more efficient. The code copies and pastes a range of cells to another sheet, ...
3
votes
0answers
77 views

Alternative for VLOOKUP/Index-Match in VBA

I am sharing this function I wrote with the community to provide an alternative to slow VLOOKUP and/or Index-...
4
votes
1answer
49 views

Collecting all relevant data from xlsm files

Is there a way to speed this guy up? The code is designed to go into a directory, open up all of the .xlsm files in the folder, and copy specific data into a target file. This code works as is, but it ...
3
votes
1answer
58 views

Most efficient way to loop through files using VBA Excel

I have a procedure for looping through all files in folders and subfolders starting at a folder which the user can select. The user can select both the source and target folder. I'm using Excel VBA ...
2
votes
1answer
67 views

Change case the data inside a column

I created a code that import the data regularly into a selected workbook. The code run well, it takes less than 20 seconds before the data is imported successfully. How does it work? I get a monthly ...
5
votes
0answers
47 views

Search for used Range.NumberFormats in Excel workbook

On an overly abused workbook we've hit the limit and NumberFormats can no longer be added. If we do want to add more we need to consolidate current formats. My code ...
3
votes
0answers
46 views

Show File/Folder Hierachy as Grouped with Indentation

I was intrigued by this question on SO and was able to come up with a reasonably efficient answer, but I'm interested in learning about further optimizations. You can certainly look at my answer to ...
1
vote
1answer
54 views

Excel VBA Userform to assist in the use of Index Match function

I often use the Index Match function in daily reporting tasks. I was looking for a quicker way to utilize Index Match, as I find the formula cumbersome to enter. I ended up creating a userform that ...
2
votes
1answer
42 views

VBA code to underline numbers in the header takes significant time and sometimes crashes excel

Background: For several of my workbooks I run Overview sheets with charts in them. When printing the Overview sheet, I put my workbook name as header, where the workbook name includes the criteria ...
6
votes
2answers
166 views

Speeding Up Excel Distance Calculation Using Bing API Calls

I am writing VB code in Excel to calculate the distance between an employee's home address and work address using Bing Maps API calls. The process follows this general flow: 1) Convert the employee's ...
2
votes
2answers
76 views

Speed Up Excel Macro - Autofilter Copying

I am using this code which works fine, but runs painfully slow. The code filters an Excel Table and then extracts only certain columns and pastes them into another sheet (in a different order). ...
1
vote
2answers
100 views

Calculations for trucks

How can I improve my VBA code to run faster? The issue I'm having is that the For/Next commands are taking ~10 hours to calculate and print all of the data. I ...
3
votes
0answers
49 views

Copying content from Excel into PowerPoint as a picture, with error handling

The code below works, but I want to ask if my solution is adequate or if there's something I should structure differently. If it's alright, I'll apply the same code in other subs. My code copies ...
4
votes
2answers
63 views

Copy paste from one sheet to another

I wrote this code and it works fine for my purpose, to look for some values and copy paste the result to another sheet (online for selected column). I want to know the opinion from this community ...
9
votes
2answers
218 views

Userform Event Listener and Emitter

It has been a royal pain that MSForm controls are missing simple events such as MouseOver and Blur, and I find myself often ...
0
votes
1answer
65 views

VBA Loop through multiple sheets to output formulas in multiple columns

I write decently sloppy code and piecemeal together various things I find online to help me accomplish what needs to be done. This loop goes through 4 different worksheets to paste a formula in 3 ...
5
votes
1answer
241 views

Speed up calculations with arrays/dictionaries

I've got a large macro which calculates diverse things for my job. I work in a contact center. Firstly it writes people and forecast data. With this calculates if there are more/less people than it ...
1
vote
2answers
41 views

Trimming strings in non-contiguous spreadsheet ranges

This code is tested and works, but I am needing some guidance on simplifying these loops with non-contiguous ranges. I was thinking an array might be better, but I ...
9
votes
2answers
450 views

Write to EXCEL from SQL DB using VBA script

I have a vba code which takes data from SQL database and dumps it into excel. I see that my query should extract approximately total of 120k records. I monitored this activity and learnt that even ...
3
votes
1answer
65 views

Consolidating several Excel sheets into one

I wrote a macro that is taking 20+ minutes to run on other computers, but when I run it on mine it only takes 5 mins. It is a larger macro and I am new to the VBA coding world. I was wondering if I ...
3
votes
2answers
144 views

VBA code to import data, doing lookups to detect duplicates

I have an import macro, which creates ID by concatenating cells, then I compare using VLOOKUP with another sheet if any duplicate found. It's running very slowly, so I want to know better ways to ...
1
vote
0answers
22 views

Update Dashboard tabs to compare subcontractor to peers then email performance summary

My macro is cobbled together using some code from Ron DeBruin and various other sources and it currently works but I am sure it is not as efficient or bulletproof as possible. Currently it updates the ...
1
vote
1answer
48 views

VBA Copy and Delete Rows loop optimisation

I'm trying to identify which cells in Column C in a spreadsheet of data are dates and if they are, then check if that date is older than 2 years old. If the second condition is met then the below code ...
0
votes
0answers
44 views

Looping through table rows and replacing key terms in a word template

Here is my code for a sub which does the following: read header of a table into an array reads first row of said table into a 2nd array loops through the first array and replaces the marked ...
1
vote
1answer
47 views

Recursive File Search Using the Dir() Function

There are plenty of examples of using the FileSystemObject to recursively search for a file in subfolders. I thought that it would be interesting to write one using the Dir() function. I was ...
1
vote
1answer
56 views

Improving speed of data clean process in vba

My code does exactly what I want it to. However, being relatively new to VBA, I feel it could be a lot more efficient - namely I think I have overused loops and worksheet functions which are slowing ...
1
vote
0answers
46 views

Running an UPDATE SQL QUERY within VBA using DAO

I have a macro in Excel that updates a recordset using WHERE a primary key in MS Access matches a primary key in a UserForm label value. The macro is called from a ...
2
votes
1answer
51 views

VBA - If value in sheet1 found in sheet2, then delete data from sheet2

I have 2 sheets setup: Exclusions and Issues Issues has a list of CASE ID's and Columns that list the "Issue" Exclusions will be populated with CASE ID's that are to be excluded (and removed) from ...
1
vote
1answer
66 views

Merging records from one spreadsheet into another

I have created the code below that compare two spreadsheets . It initially compares the range of value from sheet1 ("Status") to sheet2 ("Interface") . Whenever a range matches with the ranges present ...
1
vote
0answers
65 views

Copy and Transpose Macro VBA

I have a macro where I open several text files to copy and transpose several columns into my worksheet. As it is now this actually works. I am right now opening the text file, copying a column, going ...
-1
votes
1answer
44 views

How to return cell values of a non-sequential set of cells from multiple closed spreadsheets? Code condensing/simplification [closed]

The code below does what I need it too but it is ugly and clunky. If I could set the list of cells I want to retreive data from as an array that is declared somewhere, then I could loop through that ...
1
vote
1answer
94 views

VBA Function Error Bubbling

What will be the best practice to create an efficient error handler in object class in VBA? Creating some for Outlook (used later in both, Excel and Access) and trying to get the most efficient and ...
3
votes
0answers
78 views

Comparing times of two worksheets

The below code works however it takes several minutes to run. Is there a way to optimise performance so that it can be completed in a shorter amount of time? My code is designed to look at the "...
0
votes
0answers
98 views

VBA processing multiple files

...
2
votes
3answers
106 views

Looking for all numbers where each digit is bigger than the previous digit

This is the "Increasing Number problem", where we are looking for all numbers with \$n\$ digits where each digit is bigger than the digit before it. Or in Mathematical therms where: $$\mathit{...
7
votes
3answers
163 views

Speed up VBA with 138k rows and ~330 sheet creation

I have a VBA script that does the following and I am trying to see if I can have it perform faster than 44 seconds: start with ~138k rows of data on sheets("Data") concatenate each cell in the row ...
1
vote
1answer
51 views

Worksheetfunction.Vlookup value on error

I have this function (wb is a public workbook variable): ...
2
votes
0answers
51 views

VBA Array Functions: insert element, remove element

An extension to array functions I am building for my snake game. This one allows you to remove / insert particular elements. https://github.com/Evanml2030/Excel-ArrayFunctions API CALLS ...
5
votes
1answer
81 views

Public Function to remove empty or “” elements from a single dimension array

I've written a function to remove empty or "" value elements from an array or in other words return an array consisting only of ...
2
votes
1answer
111 views

Sluggish Loop Transferring Dictionary Data to Spreadsheet

I've added a timer to my code and the bottleneck comes when I'm looping through 47 rows and inputting data from a dictionary that I previously loaded with values. Since i use these files for a lot of ...
1
vote
1answer
60 views

Loop whose endpoint (?) change during the loop [closed]

I have a loop which insert rows on specific cases. The loop goes through ~2000 rows and insert about 500. Due to the inserted rows it doesn't loop the full range I want. I have solved that problem by ...
2
votes
0answers
113 views

VBA array functions: push, pop, shift, unshift

I want to write my snake game procedurally, using as much windows call as I can so as to practice. Looking into GetAsyncKeyState to capture keyboard inputs and play sound functions. Also making a ...
-1
votes
1answer
47 views

Standardize and reduce repeating same procedures in my coding Excel VBA [closed]

I am aware that my VBA code can be improved.I would like to standardize and reduce repeating same procedures. Can I have some guide lines for this? Where to start? I would be glad to have some ...
3
votes
1answer
60 views

Array that stores values from two ranges and compares if one cell is blank

This is my first Array in VBA for Excel, but I need some help to optimize the code and try to reduce the number of If statements. The long and short of the code is ...
1
vote
1answer
43 views

Vba to create a new column and insert array formula

My below code is the slowest sub in my workbook. I can't set it to put the Array in immediatley, because the mode is calculated incorrectly (calculates for just one ...
4
votes
4answers
175 views

A Word macro that processes trainees' dictation, highlighting errors and checking accuracy

I work in the training department of a captioning agency, where I spend a lot of time grading trainee's dictation transcripts. We receive the transcripts of trainee's dictation of test audios and ...
1
vote
1answer
91 views

Optimizing VBA Select Code

I'm trying to reword the code below to avoid using the Select and Selection terms. I tried to Range("S"...).Copy instead of .Select but that didn't work for some reason. Does anyone know a way I can ...
0
votes
0answers
61 views

Data Scraping Using VBA

I have below code, which fetch data from a Intranet. But its taking more time to fetch data.can someone help me to modify the code to increase the performance. Thanks In Advance Note-I haven't posted ...