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
3 votes
1 answer
69 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
48 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
74 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 ...
  • 121
1 vote
1 answer
56 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
158 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 ...
4 votes
3 answers
110 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 ...
  • 41
1 vote
0 answers
28 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
3 votes
0 answers
72 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 ...
  • 349
3 votes
2 answers
187 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 ...
  • 349
2 votes
1 answer
62 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
48 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
1 answer
92 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 ...
  • 111
2 votes
1 answer
104 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. ...
2 votes
1 answer
138 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
175 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
105 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
125 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 ...
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
97 views

plotting Monte Carlo Simulation from excel sheet

I have created the following code: Option Explicit ...
user avatar
0 votes
0 answers
40 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 ...
0 votes
0 answers
20 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
76 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 ...
5 votes
2 answers
395 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 ...
  • 225
7 votes
1 answer
335 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, ...
  • 225
9 votes
4 answers
355 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 ...
  • 349
9 votes
2 answers
741 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
69 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. ...
6 votes
1 answer
278 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 ...
  • 349
2 votes
2 answers
180 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
133 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
219 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
190 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,290
0 votes
2 answers
122 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
220 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
72 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
421 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 votes
1 answer
199 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
63 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,078
4 votes
2 answers
149 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
2 answers
452 views

Safe Windows API Timers for Excel

Motivation Windows API timers are notorious for crashing. I've been using them for years to workaround a nasty bug that makes User Defined Functions (UDFs) very slow in Excel when calculated in ...
3 votes
0 answers
98 views

Updating VBA code from a central repository in distributed Excel workbooks

I wrote this to help manage the code in a bunch of distributed workbooks. Maybe someday I will re-write this as an add-in, but for now, I just needed a way to manage the code without having to ...
  • 409
13 votes
1 answer
508 views

Low Level VBA Hacking - making Private functions Public

NEW: Download demo files Not sure what to title this - essentially, inspired by RubberDuck's unit test engine, I've created a way to call private methods of standard modules in VBA. It also lets you ...
  • 2,290
3 votes
1 answer
193 views

Create VBA string array fastest method

My VBA has become really rusty but I need it for now for some data downloading. This routine works but I'm certain that over the last 10 (at least) years something quicker/less code has surfaced to do ...
  • 131
4 votes
0 answers
119 views

Building VBA and VBE Menus

This is a follow up to VBEMenuButton Class. After much trial and error I abandoned the previous approach. I decided that it would be better to create a class to instantiate the menus. When the class ...
  • 4,078
1 vote
0 answers
102 views

Get & change file names from folder

This was originally posted as a Stack Overflow answer. Objective Import all filenames from a folder & change the filename using a mapping table. Challenge As described in the original posting, ...
  • 11

1
2 3 4 5
27