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,310
questions
4
votes
1
answer
90
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 ...
- 155
1
vote
1
answer
75
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 ...
- 31
1
vote
0
answers
34
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+...
- 123
0
votes
1
answer
48
views
VBA Excel - autosum values from all workbooks in the directory
I have the code such as this for opposite conditions.
...
- 259
1
vote
0
answers
54
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 ...
- 11
3
votes
1
answer
87
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
57
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
101
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
78
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 ...
- 123
4
votes
1
answer
160
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
150
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
1
vote
0
answers
32
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 ...
- 11
4
votes
0
answers
99
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 ...
- 359
3
votes
2
answers
195
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 ...
- 359
2
votes
1
answer
66
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
51
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
1
vote
2
answers
124
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 ...
- 155
2
votes
1
answer
106
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.
...
- 31
2
votes
1
answer
140
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
181
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
158
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
3
votes
0
answers
131
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 ...
- 161
0
votes
2
answers
54
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
114
views
plotting Monte Carlo Simulation from excel sheet
I have created the following code:
Option Explicit
...
thadaniel
0
votes
0
answers
42
views
Update cell format based on adjacent cell values
Trying to speed up this macro, it just loops through 'C-L" columns and changes the format based on adjacent cell values.
...
1
vote
0
answers
40
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 ...
- 11
0
votes
0
answers
22
views
Solidworks API to select Design Binder and empty the edited Design Binder
I am working on the Design Bilner macro and facing some issues with the code. Could you take a look and help me?
Solidworks has a feature called “Design Binder”. The function of this is to store the ...
1
vote
2
answers
78
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
448
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 ...
- 205
6
votes
1
answer
352
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, ...
- 205
9
votes
4
answers
366
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 ...
- 359
9
votes
2
answers
815
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 ...
-3
votes
1
answer
71
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. ...
- 5
6
votes
1
answer
358
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 ...
- 359
2
votes
2
answers
218
views
VBA functions to calculate sun and moon positions
I have the following VBA code (across three modules) to make UDFs to calculate sun & moon position data. The issue I'm facing is that they are very slow to run as I have over 6000 rows (over 10 ...
- 21
2
votes
0
answers
43
views
Collect the SKETCH NAME in the array and hunt for matching CUSTOME PROPERTY NAME and apply TOLERANCE (Bilateral or Symmetric)
I want to apply a tolerance value to my sketch using a custom property, the input for which will be provided as JSON
Here I tried to collect the sketch name from the specific feature (i.e ISO Standard ...
3
votes
1
answer
176
views
Add padding to top and bottom of cells
I have a column with variable length text in each of the cells. I have written some code to add padding to the top and bottom of the cells. While the code works well it becomes quite slow when the ...
- 131
4
votes
2
answers
248
views
Recursive function designed to ensure that full folder path exists before copying a file to the location
My recursive function works, but I feel it could be improved... suggestions? The code ensures that a given folder path exists by checking its validity or creating them if they don't already exist, so ...
- 181
2
votes
1
answer
62
views
Identify Anonymous orders in BOM list & update accordingly, execute time is taking very long
Right here, I'm trying to identify whether or not the demand order in my multi-level BOM list is made by an anonymous customer and then update accordingly.
-In laymens term, I'm checking if an item ...
- 55
2
votes
0
answers
213
views
Command line framework for VBA compiler app, written in twinBASIC :o
Ok compiler is a bit of a stretch...
I'm working on a package manager for VBA code, written primarily in python. One fiddly step of the pipeline involves "compiling" packages; this is the ...
- 2,315
0
votes
2
answers
123
views
VBA - Compare & Consolidate Dynamic Arrays
Background
The CSV files being used can be 6+ mill records in size, using a small sample size to proof the methodology.
Why are you doing this on excel? Access or SQL would be quicker
My company has ...
- 11
8
votes
0
answers
266
views
A VBA program to quantify the performance of code execution timers
Motivation
I am interested in optimising the execution time of code with short (fast) execution times on the order of microseconds (µs), but that will be used with high frequency (e.g. like ...
- 181
2
votes
1
answer
73
views
VBA, Data Split Into Two Rows, Convert To One Loop Optimization
I'm trying to convert a data set that is split into two rows into a single row. This was my solution.
...
2
votes
1
answer
480
views
Excel VBA to Fetch email addresses from inbox and sent items folders
I am using the following VBA to fetch multiple specified Email addresses from inbox and sent items folder also including cc and bcc
for eg(gmail.com;yahoo.com) must return all mails having that domain ...
-1
votes
3
answers
85
views
Populate array using elements from four source arrays
Long piece of code, I hope it is readable.
The code is basically if statements looking at 4 arrays: ArrPnLDataD1, ...
- 3
3
votes
1
answer
213
views
Automating shortages report from BOM list in excel but the total run time to output the report is quite slow. Using Excel 2010 - CLOSED
I MIGHT REWRITE THIS QUESTION IN A CLEARER WAY WITH SOME SAMPLES TOO AND POST IT AS ANOTHER SOON, AS NOW MY EXPLAINATION HERE IS QUITE HARD TO UNDERSTAND & MIGHT BE PHRASED WRONG.
I do have ...
- 55
0
votes
1
answer
64
views
importing excel data by matching and performing lookups
This code imports data from a separate sheet and matches it based on Column E's data, it does this for every cell then offsets to the next one (622*6) times. If there are any substitutions for vlookup ...
5
votes
2
answers
1k
views
Best Way to Return a Value from a Function [closed]
In my example, Method1 uses a variable to store the return values and then assigns the variable to the function. Method2 adds the return values directly to the function.
Questions:
What is the ...
- 4,068
4
votes
2
answers
168
views
Scraping campsite availability from a webpage using vba with selenium
I wrote some code to extract the information from a table, but it takes an extremely long time.
The table is in the format of a calendar.
I need the information on an Excel sheet with column 1 as the ...
- 41
6
votes
3
answers
575
views
Safe Windows API Timers for Excel
Update Feb 2023
This solution is no longer maintained here or on GitHub, mainly because I do not need a timer solution anymore. As seen below, the only purpose of this approach was to get a reliable, ...
- 1,371