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
1
vote
0answers
24 views

VBA Excel loop with offset for extended range

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
50 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 ...
1
vote
3answers
85 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 ...
1
vote
1answer
33 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. ...
6
votes
2answers
198 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-...
3
votes
0answers
55 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 ...
1
vote
1answer
95 views

Optimize Nested For loops

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 differnt format. So rather than refactor all of my model ...
0
votes
0answers
21 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
109 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
82 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 ...
2
votes
0answers
65 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
78 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
76 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
47 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
72 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
31 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 ...
2
votes
2answers
67 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
42 views

Autofill a table based on the input provided in a userform

Good morning, I have the following code: ...
3
votes
1answer
39 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
79 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 ...
2
votes
1answer
34 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
94 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....
9
votes
1answer
135 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
93 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
44 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
94 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
74 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 ...
4
votes
0answers
89 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
76 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
55 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
112 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
42 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....
5
votes
0answers
83 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, ...
0
votes
0answers
64 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
28 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" ...
4
votes
1answer
82 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
53 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
45 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
60 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 ...
2
votes
1answer
69 views

aggregate sheets from many excel workbooks into one workbook

I want to put the worksheets from several (2-5) excel workbooks (files) into one single workbook. The names of the workbooks are not standardized and so I opted for a dialog box to select the ...
0
votes
0answers
19 views

Outlook - put all ungrouped contacts in a separate folder

I have linked my icloud contacts to outlook, and wanted to display all contacts which had not been grouped. I wrote this sub for that: ...
8
votes
1answer
142 views

Data entry of Information

Couple of items to make note of first: Some of the code is inherited the other has been refactored by me. The procedures that have an underscore in them I am still working on refactoring. I am still ...
2
votes
1answer
66 views

Best practice when checking multiple addresses for a Worksheet_Change() event?

I'm doing some code revision and I'm looking at this portion of my Worksheet_Change() event: ...
8
votes
1answer
240 views

Validate an IBAN

I've found an explanation on Wikipedia on how to validate an IBAN. Since the number generated from the transformation can cause overflow with all data types available in VBA, I've worked with ...
6
votes
1answer
72 views

Survey Populate with List, Option Label and Formula, VBA

I am quite a novice. The below code does what I require but I was hoping you could help me speeding it up and simplifying it please. I know should start with basics when learning VBA..but it's just so ...
2
votes
1answer
106 views

VBA Automatic insert in a Excel Spreadsheet

Since I'm new at this site I will try to start with something very simple. I'm making an "Automatic insert" in a Excel Spreadsheet and my code is working correctly, but I'm not so sure about my way ...
3
votes
4answers
370 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. ...
4
votes
2answers
105 views

VBA to retrieve/display information of cells in excel

I am working as a validation technician for excel spreadsheets in a pharma company. Sadly & interestingly I don't know much about VBA and Excel. To validate those excel spreadsheet, I developped ...
1
vote
1answer
36 views

Calculating a Score in VBA based on selected ShapeStyle. Is there a better/shorter way to do this?

I'm calculating a score based on whether or not certain colors shape is selected. Some of the shapes are in groups, some are not. For the shapes which are in groups, only one of them will count ...
2
votes
0answers
48 views

Better ways to edit Excel through MS Access?

I have an Access macro that runs AfterUpdate of a form. It opens an excel workbook and edits a few cells. I have It working the way I want, but the problem is that ...

1
2 3 4 5
24