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,309
questions
3
votes
0
answers
191
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,411
0
votes
0
answers
42
views
3
votes
0
answers
181
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 ...
0
votes
0
answers
51
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 ...
- 83
2
votes
1
answer
102
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
98
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
2
answers
350
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 ...
- 83
0
votes
0
answers
42
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 ...
- 1
1
vote
0
answers
62
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 ...
5
votes
2
answers
123
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
4
votes
1
answer
308
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 ...
- 165
1
vote
1
answer
106
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 ...
- 41
1
vote
0
answers
50
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
74
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
63
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
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. ...
- 31
3
votes
1
answer
372
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
207
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
300
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
173
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
422
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
57
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
177
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
235
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
74
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
58
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
183
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 ...
- 165
3
votes
1
answer
118
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.
...
- 41
2
votes
1
answer
157
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
189
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
529
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
155
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
69
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
138
views
plotting Monte Carlo Simulation from excel sheet
I have created the following code:
Option Explicit
...
thadaniel
0
votes
0
answers
48
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
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 ...
- 11
1
vote
2
answers
80
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
760
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
424
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
421
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
1k
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
85
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
853
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
3
votes
2
answers
385
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 ...
- 31
2
votes
0
answers
51
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
543
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
513
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
65
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
336
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,345
0
votes
2
answers
156
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