Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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.

Filter by
Sorted by
Tagged with
0 votes
0 answers
35 views

Faster method to check if a string is found inside Word Files in a directory and subdirectories

I need to check if a string is found inside a Word File in a directory and subdirectories. I have tried to use Advanced Search of MS Windows 10, to search inside ...
Peace's user avatar
  • 113
-2 votes
1 answer
51 views

Multiple key swap algorithm [closed]

I have a dictionary with key - data pairs. My data comes from excel sheets. When I create the dictionary my objects are placed in order by their keys ex. 1 -> data of sheet1, 2 -> data of sheet2 ...
tzoni's user avatar
  • 5
2 votes
1 answer
157 views

Which is faster: Unhide rows during IF or Unhiding them all at once in another macro?

I have process where I am generating workbooks from a list. One of the tabs has varying height and I have a formula that my macro uses to determine whether to hide a row. As I move to the next ...
Mark S.'s user avatar
  • 211
4 votes
0 answers
73 views

A new Dictionary for VBA

The latest version of the Dictionary class presented in this question is available in the VBA-FastDictionary repository under the latest release. Motivation I ...
Cristian Buse's user avatar
0 votes
0 answers
33 views

Add a new sheet for all files in folder, pull data from multiple reports for each one, save PDF/xlsx summary

I'd appreciate any help people can offer to make this process more efficient and less goofy. I've created a process to loop through a folder of approximately 100 files, add a new sheet with a base ...
Kris's user avatar
  • 1
0 votes
0 answers
93 views

1d profile cutting optimization

I wanted to make a VBA that creates self-renewing combinations and I actually did it, but Excel was crashing when the combinations increased, unfortunately Excel does not work in multi-item lists. ...
ömür tokman's user avatar
0 votes
0 answers
106 views

userform with textboxes and dates to show timeline

I am writing the following VBA code to make a timeline, which I will be using for other parts of the code later on for other calculations and output. I have a userform where the following number of ...
Barbarian's user avatar
2 votes
1 answer
90 views

double click on userform running slow excel vba

I have this code for picking items with multiple values from userform and inserting them into a sheet. The code works perfect on my computer, which is pretty decent. But on a computer from work it ...
Santos Lee's user avatar
3 votes
0 answers
348 views

Iterate internal array for a VBA class

After spending a considerable amount of time poking at the internals of the Collection class, I have discovered 2 methods to iterate a private internal array within ...
Cristian Buse's user avatar
0 votes
0 answers
46 views

Loops through values searching a range of data, cuts the row of found value to another sheet

...
safo2238's user avatar
3 votes
0 answers
361 views

A vehicle database in Excel

I'm looking for some help. I'm new to coding and have started creating this Vehicle database program in Excel for a friend to use. He is wanting to be able to see all vehicles he owns, when ...
Kendall Briggs's user avatar
0 votes
0 answers
61 views

Extracting a substring based on a pattern

As part of a project to pull data from our purchasing system (Sage) back into our quoting system (an Excel template) to automate variations to existing quotes, I wanted to grab the original quote ...
Andi Allan's user avatar
2 votes
1 answer
133 views

One way Synchronize 2 Access Tables with VBA ADODB

I am synchronizing two MS-Access databases. One of them on a network drive, the other one on the local machine. (For testing purposes both in the same directory) The Databases both contain a table ...
MZiegaus's user avatar
1 vote
1 answer
155 views

Loop through cells of a range and convert to a hyperlink and change (Text To Display) to an ascending number per each row

I have used the below code Loop through cells of a range and convert to a hyperlink and change (Text To Display) to an ascending number per each row. it works, but for the point of ascending number I ...
Peace's user avatar
  • 113
4 votes
2 answers
825 views

Efficiently creating an array from a filtered sheet

[edit] I realised why everyone does this with loops; my plan was to use .SpecialCells(xlCellTypeVisible).EntireRow.Address to return the row(s) of the results of the results of the filter, but that ...
Andi Allan's user avatar
0 votes
0 answers
63 views

Speeding up VBA to add/remove Pivot Tables from Slicers in Excel

I have a workbook that has a number of Pivot Tables spread across a number of worksheets. There's a bunch of slicers on one of the worksheets, and they're pretty slow-ish to load after changing ...
bigsim's user avatar
  • 1
1 vote
0 answers
63 views

Appending data from files listed in a file

I am working on a project that requires me to write VBA code which, upon a button press, loads the data from certain files in a given directory. In my testing, I was able to load the data from 1100 ...
Leon-Josip Dzojic's user avatar
5 votes
2 answers
133 views

concatenate values and delete the remaining rows using arrays

the below code used to: Concatenate the values on a specific column "N" depend on the value of column "A" then delete the remaining rows. It works, but with range of 30k rows the ...
Leedo's user avatar
  • 275
4 votes
1 answer
609 views

Transcoding UTF-8 to UTF-16-LE in VBA

VBA is a language that's lacking a lot of basic functionality. (Pun intended) Most libraries, if they exist in the first place, are OS-specific, and even some of the inbuilt functions don't work on ...
GWD's user avatar
  • 165
2 votes
1 answer
116 views

Reviews a daily fantasy slate to check for duplicate lineups

My code below is used as a back testing tool to review a past Daily Fantasy Sports slate. This code works perfectly but when the contest size (total entrants) gets up to the 30,000 range it takes ...
safo2238's user avatar
1 vote
0 answers
56 views

Inserting Rows into Access Table Using Excel Macro

I have a macro that inserts data from Excel into an Access table. Each time it runs there may be 10k -250k rows to insert. I have experimented with using an empty database/table but it still takes 60s+...
Andy's user avatar
  • 123
0 votes
1 answer
88 views

VBA Excel - autosum values from all workbooks in the directory

I have the code such as this for opposite conditions. ...
Geographos's user avatar
1 vote
0 answers
65 views

Sub that finds Rows with same ID in Col A, then Concatenates to merge values in Col C into single cell with line break deliminater

I have a VBA that takes all the rows with the same ID in Column 1 and concatenates the data in Column 3 to a single cell with line breaks. The code then deletes the old rows. The code works, but takes ...
love2learn's user avatar
3 votes
1 answer
91 views

Iteratively paste values from spreadsheet tabs into empty spreadsheet

Basically below code is taking first 7 tabs from my current sheet and pasting them as values in a blank excel sheet with same tab names and saving them in a specific folder by filename I specified. ...
Sorab's user avatar
  • 31
3 votes
1 answer
612 views

Excel VBA to powerpoint

I am basically looking for a way to trim below code. It works just fine. This code takes a range from each tab of an excel spreadsheet and pastes it into a powerpoint file, then assigns a title to ...
Sorab's user avatar
  • 31
2 votes
1 answer
238 views

Copy and Paste Macro Containing For Loop

The macro below loops through column E in Sheet2 and finds matches in column Z of Sheet1. If a match is found it copies the value on the same row contained in column AA. It works ok but hangs when it ...
Robsmith's user avatar
  • 123
1 vote
1 answer
491 views

Optimising Read/Write Speed of Excel VBA Copy/Paste Macro

I have an Excel sheet that connects to third party software which populates Sheet1 with data. It does this multiple times per second and overwrites previous data. I have written the macro below to ...
Andy's user avatar
  • 123
4 votes
1 answer
181 views

Using a Counter to Select Range, Delete, and Shift Row Up

I'm trying to clean my excel sheet so that all lines match with each other when I put 3 different tables from 3 different data sets next to each other. The code I have gets the job done but it's ...
Milo Sandoval's user avatar
3 votes
3 answers
583 views

VBA Very Slow Performance using objects with scripting dictionary

I have a simple code that loops through a text file to fix a few things in it and I use mostly dictionaries to find and handle the adjustment. However, with larger files (about 30K lines) it seems to ...
KE_1234's user avatar
  • 31
1 vote
0 answers
108 views

Using VBA to open and SAVEAS selected Website files

As a learning exercise and to do something productive, I decided to write a VBA macro for one of my "very old" FrontPage websites. Actually I'm in the process of trying to escape from ...
RDK's user avatar
  • 11
4 votes
0 answers
205 views

UDF to Generate Hyperlinks in Excel

Background I am trying to recreate the functionality of HYPERLINK() on my own terms, as a custom UDF called Hyper_Link(). My ...
Greg's user avatar
  • 403
3 votes
2 answers
243 views

"Sudo Mode" in VBA to Let UDFs Modify Workbook

Background I am working on a VBA paradigm for modifying the Excel environment from a UDF. I call this "sudo mode", and I leverage Application.Run() to ...
Greg's user avatar
  • 403
2 votes
1 answer
79 views

Inserting Rows after Contiguous Set of Integers - Excel VBA

I have a repetitive data reduction where I have a list of ID's that I want to find in a longer list. I have a method, but it is slower than I'd like. How can can I speed this up? The process: After ...
Brandon Murray's user avatar
0 votes
1 answer
62 views

Code compares Columns "A" of two workbooks and copies different information to destination workbook with entire selected row. LastRow count slows code

Code explanation: I have a code, which performs two tasks - To open two workbooks, one being extract info and one destination and it compares the column A with Column A of these workbooks and all ...
Rokas's user avatar
  • 3
2 votes
2 answers
197 views

Sub that calls itself when its parent Workbook is closed

and no, I'm not talking about Workbook_BeforeClose. Note that the motivation of this question is Excel specific but the technique I'd like feedback on is not. If ...
GWD's user avatar
  • 165
3 votes
1 answer
121 views

Opens two workbooks, copies some ranges, close two workbooks

This code works perfectly as intended but I am fairly new at VBA and want to learn new techniques and way to optimize my code writing. It is a very simple process that involves (3) seperate workbooks. ...
safo2238's user avatar
2 votes
1 answer
170 views

Create a string "Build" number for procedures and module

The class assigns various strings to the build number. So the build might look like 3.4.5.1 which would be of the format ...
DanM's user avatar
  • 121
4 votes
1 answer
191 views

Optimizing a large report sorting macro

I've been working on this project for my organization for a few months now, and am looking for ways to increase performance on this macro. I work for an insurance company as a claims auditor. I get a ...
Dallin DeFord's user avatar
2 votes
1 answer
718 views

Hiding layers in Visio with VBA

I created (basically) an organizational chart in Visio with different levels and want to be able to hide the levels with the press of a button. If I hide level 1 (= top level), all levels below it ...
Alex's user avatar
  • 161
3 votes
0 answers
177 views

Fluent VBA: One Year Later

A little over a year ago, I asked for feedback on Code Review for a unit testing framework I created in VBA. Development of this project has been off and on for the past year. Sometimes I wouldn't ...
Brian Gonzalez's user avatar
0 votes
2 answers
70 views

VBA code which exports data from Access to Excel and then loops through the Excel file

I have a couple of VBA loops that work in the blink of eye when I execute them through Excel, but doing this as part of an Access VBA application takes like 15 minutes. The loops run through each row ...
BlueFlame's user avatar
2 votes
1 answer
146 views

plotting Monte Carlo Simulation from excel sheet

I have created the following code: Option Explicit ...
user avatar
1 vote
0 answers
43 views

makes plots associated with selected utilities

This code is applied to a master workbook with tabs containing characteristic information e.g. "Reliability standards", "Targets" etc. about each utility. The utilities of interest ...
patsy_794's user avatar
1 vote
2 answers
81 views

Copying formulae between sheets

I am currently running multiple macros on a large volume of files however watching it go through its processes today I realise one of my issue is that my loop runs through my workbooks 3 times instead ...
rmba22875's user avatar
4 votes
2 answers
976 views

Loop between two Arrays and change values based on condition

Instead of using 45 IF conditions , I put my two ranges into variant arrays. Then I used the below code to loop between them and change values of the first array ...
Leedo's user avatar
  • 275
6 votes
1 answer
445 views

Fastest function to `Remove Duplicate Lines` per each cell

The below function is used to Remove Duplicate Lines per each cell. It works without problem, but it is slow with a range of only one column and 17k rows. Actually, ...
Leedo's user avatar
  • 275
9 votes
4 answers
448 views

Compact VBA Validator for Excel Names

Update After examining the NameRulesUnicode64k.xlsm spreadsheet in excel-names, it appears that my NameIsValid() is in close ...
Greg's user avatar
  • 403
9 votes
2 answers
2k views

Small datasheet code running super slow on Excel VBA

The code has been super useful with my work datasheets. They all have this pattern and the VBA code makes sure it all has the right format. Why is it so slow? Sometimes it takes 2~3 minutes to run a ...
Afonso Medeiros's user avatar
-3 votes
1 answer
92 views

Transpose and Paste VBA [closed]

Currently from my sheet2, I wanted to copy the column C36-N36. However, each of these cells has one formula that is generated from the numbers within sheet2, i.e. C36 = A1+A2/2 something like this. ...
Jackson's user avatar
6 votes
1 answer
1k views

Filter Range VBA function for Excel

Premise I have written a function in VBA, called FILTER_RANGE(), which accepts a Range object and a filtration parameter. Unlike ...
Greg's user avatar
  • 403

1
2 3 4 5
27