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
4
votes
1answer
89 views

How to copy and paste a filtered range more efficiently

I have this bit of code that works for copy and pasting an autofiltered range. I just wanted to see if anyone knew a more efficient way to do so as it does take quite a while to complete it the way I ...
3
votes
1answer
79 views

Employee name auto-complete on card scan

Im currently running an employee name substitution on card scan. Overall the code takes the entry trough the gun scan, "scan" trough the hidden reference sheet to find a match and then input ...
3
votes
1answer
89 views

Workbook _open copy-paste reference sheet (30k+lines) from other workbook on change only

Working on finding a way to reduce in-cell formula size and memory usage of my excel workbook, i came up with the idea to "load" (copi-paste) the reference sheet i was mentioning a lot with ...
2
votes
1answer
68 views

VBA Sort-Compile-Write data from multiple worksheet on changes

I finished a code that sorts and compiles daily work data into a weekly summary. Its triggered by workbook changes so it refreshes automatically. Trying to respect "good code writing", and ...
6
votes
0answers
71 views

Shape Text Formatter Class a Builder Pattern

The preeminence is to have every method of the formatter return the formatter as a reference. This allows it to add and format text in chain. Such as: ...
4
votes
3answers
179 views

Excel Data Import and Manipulation

This code imports a list of equipment (around 8000), and then filters the errors and creates some lots for the processes that come next. The code is working, but I'm sure it can be improved, ...
2
votes
0answers
108 views

Copying Data from multiple workbooks -> Into One Worksheet (continuous data)

I have a macro created by my predecessor that I run on a monthly basis. However, it does take a while to get through, and I would like to clean it up for efficiency please. It copies data from ...
1
vote
0answers
120 views

Iteratively Exporting PDF Reports with Excel

The goal here is to notate and produce essential PDF reports. The For loop takes an id number and puts it into specified calculator worksheet. The workbook is set to automatic calculation so all of ...
6
votes
1answer
122 views

Creating a Chart Worksheet from scratch with VBA / OOP Design

I recently posted some code for review, and am now looking to gather some feedback on my latest implementation with that code. Yes, I have fallen into the OOP rabbit hole (thanks Mathieu Guindon) and ...
11
votes
3answers
581 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 ...
4
votes
2answers
139 views

A VBA Product Dictionary with a Collection of Product Services

I need to extract unique Product Group names along with its corresponding services from a table in a worksheet. The table is generated by a bot and is not filtered, I have sorted it by alphabetical ...
2
votes
0answers
55 views

VBA Excel loop with offset for extended range [closed]

Good morning, I've made the loop for the range, which is copied from one sheet to another. However I have no solution for trimming my code down, which looks like this: ...
2
votes
2answers
225 views

vba Copy Paste Transpose Link

Following takes a range & copies, pastes, transposes & links. There doesn't seem a way in vba to do this in 1 go that I've been able to find. Questions are; Is there a more efficient or safer ...
2
votes
3answers
121 views

Getting Rid of .Find in VBA Code

I have a range of two columns that I want to search for a "key" and then find the corresponding value in the directly adjacent column. The key is stored in a dictionary that I populated ...
2
votes
1answer
248 views

Part 2: Send HTTP request for each row in Excel table

This script was previously reviewed here: Send HTTP request for each row in Excel table (Part 1) I've made the changes that were suggested in the Code Review answers as well as added a few of my own. ...
7
votes
2answers
1k views

Part 1: Send HTTP request for each row in Excel table

Update: This is an older version of the question/script. The new version can be found here: Part 2: Send HTTP request for each row in Excel table I have an Excel spreadsheet where users can batch-...
4
votes
0answers
701 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 ...
3
votes
1answer
129 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
26 views

Run Teradata using Excel VBA [duplicate]

I have a simple macro moving data from Teradata to Excel. The process normally takes me 10 minutes to perform manually, however the macro can run for hours sometimes. This is making the macro seems ...
4
votes
1answer
736 views

VBA function for array of RGB color combinations and procedure to color large number of cells with unique colors

Submitting for review by experts. Inspired by this question to color value cell having duplicates with a different color. So "Apple" will have one color for all duplicates. Then "Banana&...
2
votes
2answers
146 views

Auto formatting and auto validating TextBox VBA

So, I created a class to avoid all the work behind the validation of input in a textbox. The idea is to pass an existing textbox and a type of the desired content and the class will take care of block ...
3
votes
0answers
76 views

Setting a forecast for 2,5k workers intraday for a whole month

I'm working on a large project that requires a ton of data to be processed. I was thinking that using classes would be the best way, and it is, until it has to dump out the memory at the end of every ...
3
votes
0answers
169 views

Looking for more efficient VBA to Move data from Teradata to Excel

I have a simple macro moving data from Teradata to Excel. The process normally takes me 10 minutes to perform manually, however the macro can run for hours sometimes. This is making the macro seems ...
1
vote
4answers
106 views

Excel VBA optimizing for large data base

I am new to coding and need help with a code that won't complete. I suspect it is due to the size of the data set. I tested the code using a reduced data set and it processes fine. However, my actual ...
2
votes
1answer
83 views

Compare text in two excel ranges, highlighting additions and strikethrough deletions

Code is running ok but need to be reviewed for any limitations / possible improvements. Not sure will work in all scenarios because of the number of ifs and else and length of the macro. Referred to ...
4
votes
2answers
86 views

Time axis with working days, weeks numbers, month-year

I made the bellow macro to create a time line in excel. It shows the working days, the week numbers and the month together with the year. First I start with projectstart date and projectend date. As ...
2
votes
0answers
37 views

VBA Excel bulk data loop for few columns at once

Good afternoon, I have a set of working code, which works as a loop between values i = 0-90 step 0.25. It gives 360 combinations exactly. I used to do this loop separately column by column. It takes ...
3
votes
2answers
75 views

User-defined function for estimating coefficient of performance

I am using hourly weather data to estimate performance of commercial refrigeration systems under various parameters. The formula for estimating co-efficient of performance (COP) of these systems is ...
0
votes
2answers
65 views

Autofill a table based on the input provided in a userform

Good morning, I have the following code: ...
3
votes
1answer
43 views

Write Excel range containing names to .pcs script file for PABX programming

I'm a newbie PABX tech with a hobby for programming so when I learnt most of the telephone system programming can be completed with scripts I got curious. With a bit of trial and error to work out ...
2
votes
0answers
342 views

Generating markdown documentation for VBA code

Generating documentation for VBA code The goal of this exercise to is create an application which can read module files created in VBA project and generate markdown documentation for them. Key ...
3
votes
1answer
70 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
119 views

Optimizing VBA code , Takes more than 50 minutes to update

Could you please help me optimize the macro, Its taking more than 50 minutes but still no success. The For loop is looping untill 1.0 million + rows. Screen is flickering. I have tried Application....
11
votes
3answers
260 views

Secure ADODB: Unit of Work

I've been meaning to revisit this old code for a long time, and this week finally ended up doing it. The resulting code is on GitHub, and for full context and disclosure I wrote a blog article about ...
6
votes
0answers
109 views

An over-the-top approach to data entry

My goal: to make the already easy task of filling out an excel sheet 1% easier by putting in hours and hours of effort. On a more serious note, I'm currently developing a system to manage inventory at ...
1
vote
1answer
53 views

Best way to avoid users to cancel my routine halfway through while managing other errors

I'm struggling to find the best way to prevent users from cancelling a routine halfway through (by pressing ESC, break, etc) without making sure the sheet is being locked again. On top of that, I also ...
1
vote
1answer
254 views

Adding qty If duplicates keys found VBA

Would like to have a code review so I can improve my knowledge and expertise in VBA more specifically with Dictionaries as this is the first time I used one. For context I have duplicate data and ...
1
vote
1answer
92 views

Designing a text based guessing game with a branching decision tree

I am hoping to make a quick decision tree using the if statement in excel vba. The point is to have a message with a prompt show up and then it will take the person through the prompts based on the ...
5
votes
0answers
406 views

Extract data from API using VBA & XML (Performance)

EDIT: Added a block with the raw code at the bottom (suggested by a friendly user) First post here. I sincerely hope that I am doing everything by the book. Also, I want to thank you in advance for ...
2
votes
1answer
159 views

SheetChange event VBA

I would like to ask for a code review as I feel like it's not the most efficient way of doing it. For context I have a add-ins with all the code in module and then the ...
1
vote
1answer
59 views

Altering spreadsheet data

I have the below code that helps me do some formatting. But I want to increase the efficiency of the code by making it faster. Below are the formatting steps of the macro. Convert "Q" and "S" column ...
3
votes
1answer
149 views

Array manipulation object

I'm trying to write a class that simplifies the most common operations with arrays and then I want to distribute... Maybe it can help someone... But I'm facing some problems in make the object ...
2
votes
1answer
48 views

Loop through a worksheet and insert a row with layout when value is true

I have a code that does a loop through a worksheets, if there is a value 2 inside a cell in column S, then I want to insert a row with a specific layout. I have the code, but it takes ages to complete....
6
votes
0answers
193 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, ...
2
votes
1answer
112 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 ...
1
vote
0answers
63 views

Assign random shift with restrictions

Based on this question Thought it was a nice puzzle Main idea: How to randomly assign shifts to employees based on these restrictions: As per the O.P.: Randomly write to cells "1" or "0" ...
5
votes
1answer
130 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 ...
-1
votes
1answer
64 views

Worksheet change and worksheet selection change slowing down my file

I have a file that i use as database. It has the records and in the row 1 several cells used as buttons, (open folder, copy text, send mail, ecc.) At any change on any column of any record the file ...
1
vote
1answer
49 views

VBA: Calling one sub repeatedly and scope of variables

My code as a whole works for me without issues, but what I want to improve is a small part right out of the middle of it. I hope it's ok to post a simplified snippet here that just shows the structure ...
2
votes
1answer
233 views

Performance on countifs and sumifs

I'm trying to calculate with SUMIFS and COUNTIFS some data. The output sheet looks like this: I've hidden some irrelevant or ...

1 2
3
4 5
26