Code Review

Questions tagged [vba]

Ask Question

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,077 questions
Newest
Active
Bountied
Unanswered
Filter by
Sorted by
Tagged with
5
votes
1answer
58 views

How to shorten runtime using functions other than .find

My code here loops through my folder and my files inside the folder and use.find and copy the strings into a new excel sheet and then with all the data in the new ...
3
votes
1answer
58 views

Dynamically create ordered list in outlook from Excel

I have working code for this project, but I am now in need of some help to create a dynamic ordered list within Outlook from Excel. I am trying to format comments that a user will enter into a ...
1
vote
0answers
34 views

C# Dictionary Wrapper for VBA

I am a technical reviewer/writer and I use VBA for Word a lot to manage issues in Word documents I receive. I have frequently come across niggles when using Collections, Scripting.Dictionaries or ...
5
votes
2answers
91 views

VBA Workbook to automate e-mail delivery

I started learning VBA for an internship, 3 months ago. This is the last project I have worked on. It does all the tasks it has to do, it's all about code review. I will be grateful for all ...
2
votes
1answer
54 views

VBA More effective way of data trimming

My code consists of Split, Mid, Left, Right and ...
0
votes
1answer
49 views

Reduce time taken for VBA loop when copying multiple sets of input values, performing Excel formula calculations and recording results

My VBA code currently loops for 5,000 iterations and takes about 25 minutes to collect the output data in the Output excel sheet. Is there a way to reduce the time taken for instance by improving the ...
5
votes
2answers
103 views

Source Control and custom VBA Code Exporter

Context Me and my team which I'm part of, for most of the time code in Excel VBA. Writing code in it self is pretty enjoyable, but combining our work in a shared repository is pretty painful. The ...
5
votes
1answer
387 views

PropertyComparer Class Implementing IComparer with ArrayList

By implementing the MScorlib IComparer Interface, my PropertyComparer Class enables the sorting Objects added to an ArrayList by the their properties. You can also determine if an Object is in the ...
2
votes
1answer
47 views

Add items to Dictionary from another dictionary on cell value change

I have created a very simple Class Module and a Dictionary to store original values when the workbook is opened as part of this project ive been working on. When the workbook opens I call a test to ...
5
votes
1answer
53 views

Chess game: Part 1 - Creating the layout

After working on the project for 15 hours of I had my layout set 1 row and 1 column per square, no problem. Then I decided that I needed each square to have multiple rows. I would have never guessed ...
1
vote
0answers
35 views

Round numbers in MS Word

I need to round numbers in a Word document, as I couldn't find a built-in way, here is my approach, it rounds all numbers in selection. As I use VBA mainly in Excel I'm not sure whether I've done it ...
5
votes
5answers
305 views

Simplify & Reduce steps in the IsDistinct function

Because I often have to deal with two Variants that may or may not be Null, we need a null-safe equality test, so I came up with ...
3
votes
2answers
54 views

Worksheet_Change Event Sends Email

I have functioning code that I wrote what seems like a long time ago in a Worksheet_Change event, and even though this works I'm sure that it can be written better. ...
0
votes
1answer
73 views

Copying data from a “master” sheet onto multiple “detail” sheets

I'm new to using VBA. What I'm working on is to copy details from a master file to multiple sheets within a worksheet. I'm facing a performance problem when I run my code, my Excel file keeps ...
1
vote
1answer
80 views

Looking to Optimize VBA code with Varriant Arrays instead of Ranges

The code is doing everything that I need it to. It's taking much too long though upwards of a couple minutes to complete the macro. Does anyone know how I code optimize this VBA code to run quicker? I'...
3
votes
2answers
76 views

Inherited Code - Worksheet_Change Event Code

I inherited this code and have to fix it. It does work and I know I can refactor the code using If Not Intersect(Target, Me.Range()) Is Nothing Then syntax. I am ...
4
votes
2answers
62 views

Merge duplicated cells in bulk per each column VBA

Need to merge data per column if records in the first column are the same. I wrote a VBA code and it works but it works very slow as it executes "merge" function per each row no matter if consequent ...
3
votes
1answer
85 views

Update a table based on other table

I created this simple macro that works fine for its purpose: allow the user to create a list of updates that need to be made based on groups in a target table (to basically do mass updates). However,...
4
votes
1answer
63 views

Excel Import Data from Different Workbook Dynamic Headers

I have this code below that copies data into a master workbook, in sheets "Inputs1", by matching the column headers from the master workbook with the source workbook. If a column header matches, it ...
8
votes
0answers
174 views

Tick. Tick. *breathe* BOOM! - Setting up real, stable asynchronous callbacks with WinAPI Timers in VBA

WinAPI Timers can be quite tricky to work with, as anyone who's tried to use them and fallen foul of one of the many pitfalls probably knows. Problems such as screen freezing, crashes, uncontrolled ...
8
votes
3answers
231 views

Speed up VBA with 138k rows and ~330 sheet creation

I have a VBA script that does the following and I am trying to see if I can have it perform faster than 44 seconds: start with ~138k rows of data on sheets("Data") concatenate each cell in the row ...
1
vote
1answer
73 views

excel vba discrete random variable udf

I am working on writing a discrete random variable generator function in excel vba. I've pulled this off a number of ways but here is the one I like the most. Imagine in excel we have the following ...
2
votes
1answer
101 views

Excel VBA code that writes to a Table - much slower than writing to a simple range

I have a piece of code (below) that checks a data table (45,000 rows) to see if the row matches any entries in a range elsewhere on the workbook (using custom functions). The routine then creates an ...
3
votes
1answer
98 views

VBA and SQL to return SQL results to Excel

Now that I finally have time to look back at this code I wrote over a year ago. I need help with streamlining the code I have written. I had to write a significant amount of ...
2
votes
3answers
64 views

excel vba udf: entire column as fn parameter

I have a function that accepts a range as an input. It works fine when I supply the function with a range like a1:a5, but not when I use an entire column like a:a. I could always loop over the whole ...
6
votes
1answer
53 views

Changing the ACL on a folder through Wscript.Shell within VBA

I have working VBA code in excel that changes a network folders permissions. The code uses Wscript.Shell to run icacls commands but there are multiple instances of this command and each time it runs, ...
2
votes
0answers
69 views

Intersect Method Slows Down Code In Large Selection Of Cells

I have picture in certain cells. When I run following code (With Intersect Method) in smaller number of selected cells, it runs correctly but when the number of selected cells increase - say above ...
11
votes
4answers
665 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
vote
1answer
65 views

Changing all comments' size in range to autosize

I have a code that turns all comments size to autosize, however as I have quite a few cells with comments, this code runs a little slow, to the point where it will become non responsive for awhile. <...
3
votes
1answer
80 views

OOP Dependency inversion principle VBA

I've reproduced the example of this video where dependency inversion principle is explained by Tim Corey based on C# I had some trouble understanding the scope of the Logger and MessageSender ...
1
vote
1answer
66 views

Remove row when everything in the row is same

I have a code that will delete the row if the entire row is exactly the same. So if in row 1 I have 100,200,300,400,500,600 in column A,B,C,D,E,F respectively, and in row 3 I have the same thing, it ...
5
votes
1answer
72 views

Manage Excel Styles with VBA OOP Approach (Follow up)

This is a follow up to this question Code incorporates Mathieu's comments and it works. As some parts of the review left some code to my knowledge, I ask for another review to see if I implemented ...
5
votes
1answer
58 views

Manage Excel Styles with VBA OOP Approach

I want to manage the Excel Styles in a more flexible way with bulk actions and at the same time improve my newly acquired OOP concepts. Objectives: Load the current Styles list (name and type=...
2
votes
1answer
66 views

Sluggish Performance when using ranges from two workbooks to update the one

I'm hoping someone can help me here, I'm working with a file which has about 38000 rows and 180 columns. I'm using a macro to update the fields in the one workbook with the values in the other ...
4
votes
1answer
106 views

ADODB Wrapper Class (Revisited)

I recently posted this question on my implementation of an ADODB Wrapper Class. I realized in my own review that I was missing some very important things, so much so, that I decided it would be worth ...
1
vote
1answer
61 views

Look for existing pivottables and change date/year colums based off the “Value” filter

We have a macro that updates all dates/years in all our pivottables (around 100 of them pulling out data from our OLAP cube) based off of a "Master Date" at the top of our sheet (which is being ...
4
votes
1answer
80 views

Getting Windows User Info From The Active Directory

I took some boilerplate code that I have been using for determining an active windows user's info to either send emails with their details, or to pass to a error logging class, and encapsulated it in ...
0
votes
1answer
85 views

Excel VBA - How to optimize this code with a lot of inserts

Can you please suggest how below code can be optimize? It takes around 2 minutes in Windows and 4 plus minutes in Excel 2016 for Mac. I think I will have to use write once method, accumulating the ...
6
votes
2answers
2k views

Coding Decision Trees in VBA

I have created a function in VBA as follows: ...
3
votes
2answers
96 views

Persisting Variables Over A State Loss

In an application that I built recently, I needed to persist several variables, without loosing their state, for duration that the application was in use. After some research, I found this post on ...
0
votes
1answer
77 views

VBA Excel Looping through recordset and storing the data to use later

I have a loop to add Recordset items to a Dictionary so I can do comparisons and retrieve specific data later in the code (not ...
5
votes
0answers
100 views

Extending VBA classes with Behavioral Patterns

I want to learn about Design Patterns and be able to apply them in Excel VBA. So to learn about patterns I bought a book that seems promising: Head First Design Patterns; it presents the material ...
11
votes
1answer
112 views

Recursive code to generate a parametric sweep of input values

I generate a set of data based off every combination of inputs. The source range that's inputs requires headers to identify the parameters. In its present state I'm not content as it's not as ...
3
votes
0answers
50 views

DataManager Class: Sorts Array by 1 or More Dynamically Added Columns in Ascending or Descending Order

I've been struggling to write a QuickSort algorithm to sort multiple columns of an Array for weeks. Finally, I decided to write a class that Implements mscorlib.IComparer and it has exceeded my ...
3
votes
0answers
36 views

Network connection monitor

I was recently in need of a class that checks if a computer has a valid internet connection, so I wrote one; however, I feel that there is a better way to do so and I just don't have the knowledge ...
3
votes
0answers
1k views

Scraping table data from websites

I've written a script in vba to parse table from any website if the html elements are not encrypted in javascript. There is a table index in my script in which inputting the right number it is ...
2
votes
0answers
64 views

VBA code to account for multiple users too heavy on save actions

I have a worksheet I use with a small handful of colleagues on a daily basis to share information with about orders we place. To make sure we always have the same up to date info we use a shared excel ...
1
vote
0answers
50 views

Copy Non-continuous Cell Values

Backstory: I'm creating department level workbooks that contains director-level information, so I can't use a single workbook (as easy as it would make this). I need to split (and combine) 4 workbooks ...
4
votes
1answer
81 views

Flexible Binary Search

This search consists of two functions QuickMatch() which searches for the value and getQuickRowValue() which returns the value ...
0
votes
1answer
66 views

Excel VBA Sub Routine is extremely Slow deleting a Range [closed]

The following script is very slow and I'm not sure why. The script works so if you copy and paste its a typo in the question. ...

1 2 3 4 5 … 22