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
2 votes
2 answers
69 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 ...
user avatar
  • 21
2 votes
0 answers
34 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 ...
user avatar
3 votes
1 answer
67 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 ...
user avatar
  • 131
4 votes
2 answers
117 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 ...
user avatar
  • 181
2 votes
1 answer
53 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 ...
user avatar
  • 55
2 votes
0 answers
82 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 ...
user avatar
  • 1,998
0 votes
2 answers
89 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 ...
user avatar
  • 11
8 votes
0 answers
91 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 ...
user avatar
  • 181
2 votes
1 answer
60 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. ...
user avatar
2 votes
1 answer
88 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 ...
user avatar
-1 votes
3 answers
76 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, ...
user avatar
3 votes
1 answer
161 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 ...
user avatar
  • 55
0 votes
1 answer
57 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 ...
user avatar
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 ...
user avatar
  • 4,028
4 votes
2 answers
93 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 ...
user avatar
  • 41
4 votes
1 answer
106 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 ...
user avatar
3 votes
0 answers
46 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 ...
user avatar
  • 409
10 votes
0 answers
174 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 ...
user avatar
  • 1,998
3 votes
1 answer
98 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 ...
user avatar
  • 131
4 votes
0 answers
54 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 ...
user avatar
  • 4,028
1 vote
0 answers
42 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, ...
user avatar
  • 11
5 votes
1 answer
66 views

VBEMenuButton Class

The VBEMenuButton class simplifies creating menu items in the VBE Editor. When Excel opens, the Auto_Open() routine in my Personal Macros book is triggered and my ...
user avatar
  • 4,028
17 votes
0 answers
236 views

Progress Bar for Excel

Any future updates can be found at Excel-VBA-ProgressBar What I've been using so far For the last 6 years I've been using a progress form which I wrote in VBA using just Windows API calls. The code (...
user avatar
0 votes
0 answers
38 views

Syncing Multiple PivotTable Slicers using VBA

I am extremely new to VBA code; however, I have found a resource online that has helped me sync my PivotTable Slicers in Excel. The code seems to work exactly how I want it, but it takes approximately ...
user avatar
  • 1
2 votes
1 answer
69 views

Excel VBA Hide Rows Based Upon List Selection Performance

I currently have a worksheet with approximately 40K rows and 108 columns that I am working with. What I am doing is creating a list that when user selects a particular text in that list then certain ...
user avatar
  • 23
5 votes
1 answer
79 views

Produce date range string from a database table

My project creates a report based on test results which are stored in database table associated with a JobNumber. The test results for a particular JobNumber may be ...
user avatar
-1 votes
2 answers
80 views

Verify items and prices in a large Excel sheet

This code searches for a lookup value (Worksheet "Price", Column B13:B31) from the table array in (Worksheet "Sheet1", Column A4:Z10000). Can this be done in a simpler or shorter ...
user avatar
-1 votes
2 answers
70 views

Speed up before/after refresh subs

I have the following VBA subs that run before and after query refresh. However, it's taking quite a while to run through the code (about 20 seconds or so from clicking 'Refresh All' to being able to ...
user avatar
4 votes
0 answers
126 views

Print apartment mailbox lists from excel

Update Due to the fact that the client's version of excel does not support VBA, I have moved on to a C# excel interop solution which has been reviewed. The test data has been added at the bottom. The ...
user avatar
  • 18.2k
1 vote
1 answer
115 views

Convert a Double to a string with a fixed length in VBA

I am converting a double to a string with a fixed length (6 characters in this case). A minus, point or E also count. This seemed like a very simple task in the beginning, but I ran in to many issues. ...
user avatar
  • 111
2 votes
0 answers
80 views

SQLite C/ADO VBA library with reflection

SQLite C/ADO VBA library with reflection The SQLiteCAdo library is a VBA middleware facilitating access to SQLite databases. Its two subpackages provide alternative connectivity options: via ADODB/...
user avatar
  • 375
1 vote
1 answer
69 views

Ordered unique union function

I made a user-defined function union in VBA, such that: it could take variable parameters each parameter is a one-column range like ...
user avatar
  • 113
1 vote
0 answers
49 views

Resolving circular references in VBA via ObjectStore class and CleanUp cascade

Resolving circular references in VBA via ObjectStore class and CleanUp cascade I have been working on a VBA database library and realized I had a few circular reference loops in my class hierarchy, as ...
user avatar
  • 375
1 vote
1 answer
83 views

Predeclared class instance tracks the number of regular instances

Predeclared class instance tracks the number of regular instances In VBA, predeclared class instances can act as object factories, and in general, it makes sense to keep this instance stateless. ...
user avatar
  • 375
3 votes
1 answer
80 views

Checking if date falls within a date range then write the date range to a cell

I want to simplify some code I have written but not sure how to go about doing it. When my workbook opens it checks the current date and compares it to dates I have on a sheet. If the current date is &...
user avatar
0 votes
1 answer
38 views

Macro which performs a series of operations on a given set of data

This macro calculates some basic figures from a given set of data. But it runs very slow probably due to me referencing the sheet again and again. Does anyone have a better idea on how to optimize ...
user avatar
2 votes
0 answers
102 views

Evaluate performance of DLL calls from VBA

While playing with a VBA project involving Windows APIs, I have noticed some odd behavior: API calls under Excel 2016/VBA7/x64 appeared to be much slower than under Excel 2002/VBA6/x32. Since I ...
user avatar
  • 375
4 votes
2 answers
1k views

VLOOKUP Macro to reformat data between sheets preserving the connection to the source

Hello I'm relatively new to VBA and I have this macro to VLookup information into corresponding cells in the sheet that works, the issue is that it depends on a For Loop to iterate through 10s of ...
user avatar
  • 57
1 vote
2 answers
64 views

Copy the last 125 rows from a selected excel file and paste into the used one

My task was to get the last 125 rows from an excel workbook. The rows are started from the 17th row and it goes until it can. Here's my code: ...
user avatar
2 votes
0 answers
110 views

My first vba-selenium code

I have been obtaining financial information on an annuity I have which is invested in multiple funds which are proprietary to the insurance company. The asset value is not available other than on ...
user avatar
2 votes
2 answers
69 views

Comparing Multiple Columns to Remove Duplicates

This code compares data from two spreadsheets generated from our clinic's software. The goal is to eliminate all the rows from the PaymentsSheet (our most recent ...
user avatar
4 votes
0 answers
199 views

VBA class managing loading DLL libraries

I am playing with a custom-built SQLite library intending to access it from VBA via the C-language API. I keep the library within the project folder, so I load it via the LoadLibrary API. To make the ...
user avatar
  • 375
3 votes
1 answer
150 views

Collecting all instances of an Office application (Excel, PowerPoint, Word)

A project of mine required me to search for certain workbooks in open instances of Excel. I found this article Code to get Excel, Word, PowerPoint from window handle which returns the applications ...
user avatar
  • 4,028
1 vote
0 answers
46 views

Outlook VBA to Replace Tokens from an email template

I'm creating a few macros that open specific Outlook templates an allow you to replace specific tokens before sending the email. It seems to work, but I'm admittedly doing full loops when I probably ...
user avatar
  • 111
2 votes
1 answer
190 views

=PRINTF("{1} - {2}", ... ) in Excel without VBA - LAMBDA functions with variable number of arguments

The TEXTJOIN function has a really nice interface: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], …, [text252]) ... where ...
user avatar
  • 1,998
3 votes
2 answers
123 views

Sorting worksheet by a given column

My code runs exactly as it should. However, I would like to make it run a bit faster. I have tried defining some variables as Long to run it faster but it is still ...
user avatar
0 votes
0 answers
45 views

Creating Pivot Table taking time at average vales

I have created a VBA macro to create a pivot table automatically. But taking 15 to 20 minutes of time to complete the macro. Especially at the point of below code taking half of the time. Review Code ...
user avatar
2 votes
2 answers
118 views

Forms to enter trading information

My project has grown for a year and now comprises of 19 standard modules, 7 class modules 11 forms. Some of the forms appear to freeze when called but, in fact, they are just slow to respond, taking a ...
user avatar
  • 141
0 votes
1 answer
93 views

DbRecordset: Updatable recordset with transaction and affected rows count check

DbRecordset is a new class added to a fork of the SecureADODB library, which wraps the ADODB.Recordset class. It complements the functionality of the DbParameters class from the previous post and ...
user avatar
  • 375
-3 votes
1 answer
65 views

How to reduce the number of loops? [closed]

The following code works perfectly. It just works very slowly. Is there any way to speed up/optimize this code? I think its the amount of loops but I am unable to figure out how to reduce the number ...
user avatar

1
2 3 4 5
26