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,299 questions
8
votes
0
answers
131
views
Faster VB6 / VBA class deallocation
VB6 / VBA is slow to deallocate class instances
VB* class instance deallocation becomes exponentially slower the more instances of that particular class there are.
Quick test. You will need:
Guido's ...
- 1,626
8
votes
2
answers
269
views
VBA: Efficiently Organise Data with Missing Values to Achieve Minimum Number of Tables
The task is to create sub-tables from an original table (like below) that contain the missing data columns (missing data is highlighted in red). The important part of this task is to make the smallest ...
- 81
3
votes
1
answer
124
views
VBA code used to build a diverse portfolio of DFS lineups to minimize risk
This code works great and does exactly what I want but it takes around 10 minutes for the code for find 150 lineups. I am hoping to get help and learn how to make it much faster.
The goal of this code ...
- 81
3
votes
0
answers
80
views
Evaluating WinAPI, PowerQuery, PowerShell, and FileSystemObject for File Retrieval Using VBA
Traditionally, VBA developers rely on the FileSystemObject to retrieve files within a directory. I decided to test and compare different methods (Windows API, PowerQuery, PowerShell, and ...
- 4,308
3
votes
1
answer
89
views
Search through all the sub-folders in a given folder for files
I use the below code to loop through a list of data and search through all the sub-folders in a given folder for files which match my requirements.
Right now it takes this code about 22 minutes to run,...
- 33
3
votes
0
answers
136
views
Simulating Objects with Collections in VBA
Background
I am writing a VBA module which requires the functionality of a custom object. This object will support "regular" functions (UDFs) within the module.
Due to reasons beyond the ...
- 529
4
votes
1
answer
111
views
Speed up recursively looping through network folder and subfolders in VBA
I'm trying to loop through around 100k files in varying degrees of nested directories using vba and md5 hashing each of those files. Right now, the current state of the program takes around 1.5 to 2 ...
- 43
5
votes
1
answer
118
views
Update Member "In Place" within VBA Collection
Background
I encountered this issue a while back, where I tried to "set" the value of a member "in place", without changing its position within the ...
- 529
3
votes
0
answers
348
views
Fluent VBA: Two (Almost Three) Years Later
Part 1 - A fluent unit testing framework in VBA: A fluent unit testing framework in VBA
Part 2 - Fluent VBA: One Year Later: Fluent VBA: One Year Later
Two (almost three) years have now passed since I ...
- 201
0
votes
0
answers
45
views
Alternative Solution for Converting Word Shapes to Images for UserForm
I am trying to convert shapes embedded in a Word document into image files so that I can display them in a UserForm. My current approach involves using Microsoft Publisher to save the shapes as image ...
- 109
-1
votes
1
answer
66
views
Need assistance modifying a "too large" procedure [closed]
I have a chunky VBA script that needs some help making it efficient, which I think will correct the "Procedure too Large" issue I just ran into.
I run the procedure from an open workbook ...
0
votes
0
answers
54
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 ...
- 113
-2
votes
1
answer
58
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 ...
- 5
2
votes
1
answer
183
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 ...
- 211
5
votes
0
answers
144
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 ...
- 1,626
0
votes
0
answers
39
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 ...
- 1
3
votes
1
answer
139
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 ...
- 41
2
votes
1
answer
101
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 ...
- 21
3
votes
0
answers
397
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 ...
- 1,626
4
votes
0
answers
405
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 ...
2
votes
1
answer
149
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 ...
- 21
1
vote
1
answer
175
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 ...
- 113
4
votes
5
answers
927
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 ...
- 103
5
votes
2
answers
135
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 ...
- 275
5
votes
1
answer
704
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 ...
- 175
2
votes
1
answer
119
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 ...
- 81
0
votes
1
answer
94
views
VBA Excel - autosum values from all workbooks in the directory
I have the code such as this for opposite conditions.
...
- 259
3
votes
1
answer
98
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. ...
- 31
3
votes
1
answer
625
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 ...
- 31
2
votes
1
answer
251
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 ...
- 123
1
vote
1
answer
525
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 ...
- 113
4
votes
1
answer
186
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 ...
3
votes
3
answers
620
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 ...
- 31
0
votes
0
answers
118
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 ...
- 9
4
votes
0
answers
215
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 ...
- 529
3
votes
2
answers
249
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 ...
- 529
2
votes
1
answer
82
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 ...
0
votes
1
answer
66
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 ...
- 3
2
votes
2
answers
203
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 ...
- 175
3
votes
1
answer
125
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.
...
- 81
2
votes
1
answer
179
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 ...
- 121
4
votes
1
answer
193
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 ...
2
votes
1
answer
789
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 ...
- 161
4
votes
0
answers
205
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 ...
- 201
0
votes
2
answers
76
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 ...
2
votes
1
answer
148
views
plotting Monte Carlo Simulation from excel sheet
I have created the following code:
Option Explicit
...
thadaniel
1
vote
2
answers
83
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 ...
- 21
4
votes
2
answers
994
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 ...
- 275
6
votes
1
answer
451
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, ...
- 275
9
votes
4
answers
463
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 ...
- 529