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,289
questions
2
votes
2
answers
90
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 ...
- 274
2
votes
0
answers
36
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 ...
- 2,573
3
votes
1
answer
71
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 ...
- 1,001
-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, ...
- 4,383
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 ...
- 4,028
3
votes
1
answer
90
views
Calling an Excel User Form from Outlook
I have an Outlook Module that contains the VBA Code below. The VBA code opens an Excel Add-In called TMAPAddIn.xlam, which then displays a User Form. I exported the Module and gave it to other ...
- 1
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 ...
- 953
2
votes
0
answers
84
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 ...
- 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 ...
- 953
18
votes
0
answers
243
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 (...
- 1,001
2
votes
3
answers
122
views
VBA Vlookup to work faster
I have a code in vba that works well but it is slow so I am looking for a better solution to this code.
If there is any one who could help me I would be grateful!
...
CommunityBot
- 1
5
votes
1
answer
111
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 ...
- 1,001
8
votes
0
answers
93
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
5
votes
2
answers
192
views
VBA code for testing efficency
I am currently trying out code for timing efficiency (how fast it runs, basically).
The general consensus is that code with ActiveCell,...
- 57.8k
18
votes
3
answers
6k
views
VBA Code Profiling
I have wanted a way to do code profiling in VBA for quite some time.
It becomes very complicated to figure out what methods are actually being executed for how long and how often in complex Access ...
- 20.3k
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.
...
- 953
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 ...
- 55
2
votes
1
answer
90
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 ...
- 4,383
4
votes
1
answer
847
views
Excel Chart - Crosshair cursor lines with VBA
Submitting for expert review.
I am trying to create a Chart - Crosshair cursor in excel chart sheet. Chart - Crosshair cursor lines are two lines (one horizontal and other vertical) moving along with ...
CommunityBot
- 1
7
votes
2
answers
1k
views
Evaluating formulas greater than 255 characters
Recently I was receiving Error 2015 when trying to use Application.Evaluate, then I realized it was because my formula was greater than 255 characters. As a ...
- 247
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 ...
- 953
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 ...
- 1,998
4
votes
2
answers
94
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 ...
- 446
3
votes
0
answers
47
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
10
votes
0
answers
177
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 ...
- 1,998
6
votes
0
answers
215
views
Chart Based Animations In Excel VBA
I've been working on re-writing comfortablydrei's answer to this PPCG Question regarding creating a illusion animation in pure Excel VBA. So far, I've been able to rewrite the code to function in VBA, ...
3
votes
1
answer
100
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 ...
- 889
4
votes
0
answers
55
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,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, ...
- 57.8k
12
votes
5
answers
813
views
Assign a variant to a variant
Quick review:
I've been writing API code which receives user input that can come as either an object or a value type - a problem outlined here.
In order to write an unknown data type to a variable, ...
- 1,998
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 ...
- 1,998
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 ...
- 24.8k
2
votes
1
answer
70
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 ...
- 74.6k
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 ...
- 18.3k
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 ...
- 953
-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 ...
- 243
-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 ...
- 32.4k
2
votes
1
answer
194
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 ...
- 1,998
4
votes
3
answers
280
views
Generating a bill of materials in Excel VBA
I am not an advanced user of Excel and am VERY new to VBA. My only programming experience is 2 C# classes in college. That being said, go easy on me ;)
I am working on a team that audits military ...
- 142k
1
vote
1
answer
118
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.
...
- 111
2
votes
0
answers
84
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/...
- 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 ...
- 4,028
4
votes
0
answers
200
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 ...
- 375
2
votes
0
answers
103
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 ...
- 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. ...
- 953
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 ...
- 375
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 ...
- 51
3
votes
1
answer
81
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 &...
- 18.3k
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 ...
- 51
10
votes
2
answers
829
views
A more readable InStr: StringContains
Consider the following:
If myString = "abc" Or myString = "def" [...] Or myString = "xyz" Then
In C# when myString == "abc" ...
- 51