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,207
questions
0
votes
0answers
12 views
Copy a block of data into existing cells, adding a new line
Wondering about efficiency here - I have a bit of code that does what I want; but it is terribly slow. I structured the code in a way that made sense to me logically, but I'm wondering if someone else ...
5
votes
1answer
108 views
Filter a two dimension array
This is the function I've wrote to filter a two-dimension array.
I use it mainly on forms with user-defined filter (categories, dates, search bar, etc).
It works fine, but it's ugly.
Do you have ...
4
votes
1answer
73 views
Copy data from a pdf into word for the purpose of starting a report
In order to write some reports I have to manually copy the name of client, address, claim number, etc from a pdf that I receive. I want to automate this part of the report writing by using vba to copy ...
3
votes
5answers
468 views
Find duplicated text for tasks in MS Project
My code in MS project VBA, in theory, doesn't do anything major.
It compares the tasks with each other, but it runs super slow.
One run through Temp lasts 15 secs. ...
2
votes
1answer
104 views
OOP approach to filter two-dimensional array
So, after this functional(?) approach and thanks to the great improvement of @CDP1802, I've decided to try an OOP approach to filtering an two-dimensional array.
In my opinion, the result is way more ...
6
votes
2answers
3k views
Sending test emails to a list of email addresses
I've made the following program that takes a list of email addresses from a table in MS Access and sends each a test email.
...
5
votes
2answers
99 views
excel-vba function to filter an array and return an array
I have a function to filter a 2-d array.
It works. But I am not sure if the following mechanism is a sensible concept.
The idea is:
loop over the input array which is received as ...
8
votes
0answers
277 views
Private VBA Class Initializer called from Factory #2
About
This is a follow-up of a previous question Private VBA Class Initializer called from Factory. I've decided to create a new question instead of answering my old question because I would like the ...
5
votes
0answers
169 views
Fast Native Memory Manipulation in VBA
I've been quite annoyed lately by the fact that the CopyMemory API (RtlMoveMemory on Windows and ...
1
vote
1answer
45 views
VBA Excel substitute function for multiple conditions at once
I use the SUBSTITUTE() function in my VBA code.
In Excel formula, we can nest it as shown in the thread below:
https://stackoverflow.com/questions/22313965/how-can-i-combine-multiple-nested-substitute-...
18
votes
2answers
3k views
Down the rabbit hole with MVP
Following-up on this post where I implemented a Repository Pattern in vba to abstract ADODB and enable testing my Excel app without hitting a database backend; curious about how far vba would let me ...
3
votes
1answer
66 views
Create Table After Deleting Rows Before Desired Range and Filter to Delete All Other Unnecessary Rows
The code below first searches for the first searchText and deletes all rows that precede it to establish a range for a table object. Once the table object is ...
6
votes
2answers
174 views
Copy a contiguous sub-column of cells
This code searches for the word "Disbursements" and copies all of the rows with data below it, including the row with the specified word. After the rows are copied to a new sheet, the ...
0
votes
0answers
62 views
Performance issue “not enough memory” Macro vba Excel - Parsing data
I have built some code that should parse the data according to a unique value and then create a new Worksheet for each unique value. My initial table has 10 Columns and around 25K rows. The code works ...
41
votes
4answers
11k views
A lightning-fast StringBuilder
In the process of trying to build a serializable data structure, I found myself building large strings, which gets very slow because VBA copies a string every time concatenation is performed.
To ...
-1
votes
1answer
55 views
Is it possible to make this macro faster? Copy and paste when condition is met [closed]
I have a problem with macro running too slow and I guess it is just because of lack of my knowledge.
I have a macro that is copying data from "database" and paste it to another sheet. Macro ...
0
votes
0answers
37 views
VBA - create separate spreadsheets from a filtered table
The task is relatively simple and until now it was always performed by some poor admin soul. There is a large table that needs to be filtered based on a simple criteria and each view has to be saved ...
4
votes
1answer
39 views
Excel VBA Code for Imperial Length Measure Conversion and Formatting Yards Feet and Inches for English and Spanish
STR2INCHES is an Excel VBA Function that will convert string text for Imperial length measurements to a decimal number of Inches.
It handles both English and Spanish words, abbreviations and symbols ...
2
votes
1answer
70 views
How to speed up a treeview population in Access?
I have the following function that causes a significant visual jittering and lag in my access database - This is a treeview selection based on data in a table (tblDATA) with the fields:
Object | ...
2
votes
1answer
95 views
Searching and sorting ranges in excel with toggle buttons
I have a spreadsheet containing ±100 named ranges (5 rows, 15 colums) which are sorted based on time.
My plan was to make toggle buttons to hide or unhide named ranges when they contain a certain ...
-1
votes
1answer
95 views
VBA how to speed up the code
Hey I have the following code written in VBA. It simulates trajectory of asset price (BS_trajektoria_2 function) and calculate payoff from option, This procedure is ...
2
votes
1answer
44 views
VBA, Transferring of Data from one File to another
I have this code that:
Clears my worksheet from row 12 and onward in Workbook A
Opens a new file Workbook B
Copys the sheet to my Workbook A. and Close Workbook B
Renames sheet in Workbook A
...
2
votes
1answer
118 views
Excel VBA speed up code to hide rows/delete color
I am using following code to delete color and hide rows that include several criteria before exporting sheets to pdf's. Is there any way to speed up this process as it is taking quite a lot of time to ...
5
votes
3answers
1k views
Creating JIRA task from Email in MS Outlook
Every day I get 5 to 30 Appointment emails with almost the same content from which I need to create the Tasks in Atlassian Jira. I wrote a macro to automate this process. I've never written anything ...
7
votes
1answer
221 views
Private VBA Class Initializer called from Factory
I've been playing with COM lately and while getting to understand the mechanism of how class methods/properties are called an idea came to mind: what if we can have a global instance of a class that ...
4
votes
1answer
865 views
Converting a number into Hungarian text
I'd like to convert some integer number from a cell between 0 and 1,000,000,0000 into Hungarian text. I've found already a solution, a VBA function.
How can be improved this solution?
...
2
votes
3answers
47 views
Count the status messages in a column VBA
Can someone review my code and provide feedback. For context in column A will be a status message of "OK" or "NOK" and this little function just counts the number of times this ...
2
votes
1answer
107 views
Different worksheets data comparison VBA is it possible without Application.WorksheetFunction.Vlookup?
I wrote this code to speed up my daily work. I want to ask for help on how to further accelerate this code.
Operation of the code
It copies 1-1 worksheets from 3 different workbooks to 3 worksheets of ...
2
votes
0answers
51 views
Modeling data from Excel sheets to parse to Excel sheet
Background:
I have close to 50 excel files - I cannot change the data source - and I steadily get more.
My task is to make sense of all that data and, lo an behold, save that as another excel file (I ...
3
votes
2answers
53 views
internal links b/w sheets of merged workbooks, using VBA (EXCEL MACROS)
My aim is to merge all workbooks having multiple sheets from any specified folder to one workbook of multiple sheets. The problem is I don’t want external links to be maintained, If I use "...
3
votes
1answer
123 views
COVID model using UVA data
I have built a COVID model using UVA data. Currently that data is unavailable so I'm using another source. The new source is, of course, a different format. So rather than refactor all of my model ...
1
vote
0answers
38 views
EscapeString Class for MySQL and JSON Using the Builder Pattern
This class is being used to build some complex strings JSON and MySql.
Note: I'm not using the JSon converter because is difficult to create the structures the at that I want them and MySQL require ...
3
votes
2answers
109 views
How to get the median value faster
Using Excel, I have one sheet ("db") with data extracted from MES.
I have to analyze this data and I need: Articolo, Fase, Tipo Confezione, TURNO, Macchina, avg and median.
I searched ...
4
votes
1answer
957 views
Creating two dictionaries to lookup values into an array
In my attempt to help improve Using Array to store calculations in VBA, I figured a good way to do it would be to create two dictionaries of values to lookup.
So this was my attempt at creating two ...
1
vote
1answer
81 views
Looking for a more condensed method of assigning values by using arrays and fixed ranges
I currently have a Template Workbook set up that has charts and graphs updating when new data is plugged into the Data Tables.
The Data Tables are fixed ranges, and won't change.
The Macro is within ...
18
votes
2answers
3k views
Using events together with interfaces in VBA
Introduction
Because of the limitation of VBA in using events in interfaces I was searching for a kind of workaround.
For sure I also read this which also provides an approach, but I was searching for ...
3
votes
1answer
51 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
vote
0answers
46 views
VBA Data dump code using transpose and timeline duplication
The below code ran in 2:26 which is way too slow especially as it's only 1/3rd of the total (the remaining 2/3rd does something very similar just on different sections). Is there something I can do ...
2
votes
1answer
172 views
Excel VBA code that displays a 14 day weather forecast
I am trying to see if anyone can help me in making this Excel VBA code a little faster in rendering the data from the weather site I am using. Maybe instead of getting all 14 days it includes in the ...
1
vote
0answers
47 views
Trying to speed up VBA macro to generate viable Daily Fantasy Sports lineups
I have created the code below to generate daily fantasy sports lineups from a pool of players that have been entered in my excel file. Right now the code can run through about 16,500 combinations per ...
2
votes
1answer
99 views
Output the rounded sum of each column in selection
I've written something (i.e. frankensteined from stack exchange) that appears to work but I haven't done much testing on the edge cases. Signed up here for some feedback on what optimizations or other ...
10
votes
0answers
192 views
Host-Agnostic Model-View-ViewModel Infrastructure
I've written an API that unlocks some serious MVVM capabilities with VBA's MSForms UI framework.
This is the complete code-behind for an example ...
2
votes
0answers
39 views
Bulk Email Sending Using VBA Run Time Concern
I have written a code for my purpose to send bulk email dynamically (Name,Document,Expiry date,email address will be changed dynamically)..Everything is working fine but it take a approximately ( 4 ...
10
votes
3answers
436 views
Simulated WeakReference class
I've created a fake WeakReference class for VBA by chance. A Weak Reference is not counted (i.e. IUnknown::AddRef method is not called). I knew about the Weak Reference concept from Swift and I ...
24
votes
0answers
340 views
Pacman implemented in OOP VBA
What?, Why?
I have been inspired by several other posts on the topic of OOP implementations in VBA to try and create a Pacman clone. I think this task is not all that hard in most languages; but, I ...
6
votes
1answer
108 views
VBA Error Handling Sub, Working for all subs
The idea is to make an error handling procedure, which prints messages in DEV mode and shows MsgBox in ...
3
votes
0answers
57 views
Taking time off of taking time off
In my ongoing quest to automate everything I have to do at my job, I've decided to learn how to do webscraping with VBA. I wanted to start with something easy, so the following simply fills out a ...
4
votes
2answers
452 views
Extract data labels from a chart in PowerPoint to Excel
Would like to check if anyone here is able to simplify the following code.
What it does: Extract data labels value from a chart in powerpoint to excel.
...
1
vote
0answers
56 views
Longer Macro getting stuck when opening Workbooks
I have a longer Macro has sub functions opening many different Workbooks, takes the data, and pastes into a Destination Workbook through arrays and .value. There ...
13
votes
1answer
539 views
“String Interpolation in {language}”, “VBA”
"This is {0} cool!", "freaking"
I've always wanted an easy and intuitive way to inject variables into a string. So after about 10 variations, I finally came up with this function.
How it works
The ...
