Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

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
1answer
49 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
86 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 ...
0
votes
0answers
12 views

Changing the value of a cell to have the same cell output a result? [closed]

How can I change the value of cell B2, which is 10, to become 100? Also, I want it to be applied to a range of cells in the same column, B2:B13. How can I do that? One more question, can I have ...
3
votes
1answer
74 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
62 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
47 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, ...
11
votes
4answers
652 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, ...
3
votes
1answer
66 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
52 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
62 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
52 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=...
1
vote
1answer
63 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. <...
2
votes
0answers
65 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 ...
2
votes
1answer
63 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
74 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 ...
3
votes
1answer
54 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 ...
3
votes
2answers
91 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 ...
5
votes
0answers
97 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
49 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 ...
0
votes
1answer
70 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 ...
3
votes
0answers
35 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 ...
7
votes
0answers
137 views

Tick. Tick. *breathe* BOOM! - Taming the SetTimer API

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 ...
2
votes
0answers
62 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
79 views

Flexible Binary Search

This search consists of two functions QuickMatch() which searches for the value and getQuickRowValue() which returns the value ...
4
votes
1answer
98 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 ...
0
votes
0answers
29 views

Performance of ID's checking between Access and Excel

I want to increase the performance of this code: ...
2
votes
1answer
121 views

ADODB Wrapper Class

I use ADO (specifically for accessing SQL) daily at work. So, I finally decided that I was going to create a class that makes it simple for myself and other programmers on the job to use. Just the ...
0
votes
1answer
64 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. ...
8
votes
1answer
99 views

VBA - new built-in enumerations and backwards compatibility

msoGraphic is an MsoShapeType enumeration that is available in Office PowerPoint 2016 and later (versions which can handle svg graphics). If you attempt to check an MsoShapeType against msoGraphic in ...
1
vote
0answers
78 views

Using arrays instead of regular loops

I have a working code and it works fine for small amounts of data ...
1
vote
1answer
60 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 ...
5
votes
0answers
109 views

Factory class that uses reflection to instantiate objects by class name

In this SO post Dynamically choose class from string Alexander Platonov creates a Function on the fly and then uses Application.Run() to instantiate an Object by ...
6
votes
1answer
203 views

Excel's Dynamic Array Functions…In VBA (No Win 32)

In recent months I have been trying to figure out how in the world one can mimic the functionality of Excel's New Dynamic Arrays exclusively in ...
2
votes
1answer
353 views

VBA loop optimization

Hi folks: I posted a SO (https://stackoverflow.com/questions/57541246/optimizing-vba-function-loop) and was told to ask here. I am starting to think that rather than relay on excel as my data ...
4
votes
1answer
261 views

Compare two Excel sheets by Cell Content

This code is a result of a lot of help from this community. What does it do? It compares two excel sheets cell by cell and copies the differences into another excel file. I’m trying to modify the ...
4
votes
4answers
159 views

Find differences between two spreadsheets

I have this code that is supposed to compare two Excel sheets. The code is working fine for small comparisons. I did a test run with 7 rows and 2 columns. The code itself works as follows: it ...
1
vote
1answer
37 views

Clean up code that transfers data from DB2 Server to Excel

The code below is functional and works as expected, but I imagine there is a better way to test for the error that I am testing for . The scenario is that data is transferred between two different ...
4
votes
5answers
125 views

Turn On/Off Functions to speed things up

I have these two macros that are like my Yin & Yang. They turn off and on functions in excel so my macros calculate quicker. I call on in the beginning and one at the end. Is there anything I ...
0
votes
1answer
81 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 ...
0
votes
0answers
50 views

Can Excel Vba code be updated via an Excel Add In for multiple users

I have created an XLAM file called "MyCustomAddIn" from an XLSB file called "Call Tracker" which contains all my Subroutines, Functions and UserForms. I have referenced "MyCustomAddIn" inside of "Call ...
3
votes
1answer
75 views

Excel VBA code that copies data from one sheet to another

I have written a VBA code that copies data from source sheets into destination sheets. The destination sheets are formatted as a table so that formulas can be calculated automatically to update a ...
4
votes
1answer
48 views

Exporting array of tabs to new workbooks, save them based on tab name

Brief: This is a question geared towards optimization and general better coding practices. I'm self-taught in VBA so I'm cautious of bad practices, but they do come up. My Reasoning: I break the ...
6
votes
2answers
98 views

Statistical User-Defined Functions (UDF)

Problem The following statistical functions were created to calculate statistics of experiments: Mean Peak to Peak Standard Deviation Variance Mean Absolute Error (MAE) Mean Square Error (MSE) Root ...
0
votes
0answers
81 views

Excel: read data into ADODB recordset

I wrote the function below, but I feel it could be improved and so I am open to your wise suggestions. The use case for this is to manipulate Excel data. As a freelance dev, I cannot always choose the ...
2
votes
1answer
41 views

Reads files into 12 different sheets & cross compares files with audits and charts it out

I was wondering if anyone has any improvement ideas for my code. At the moment it is somewhat sluggish though it does work. This is my first project in VBA; I come from a background in PHP/Python. <...
1
vote
2answers
64 views

Macro for transforming 2 way matrix into 1 way matrix

I have written some code that transforms 2 way matrices into 1 way matrices. Although it's comfortable to use, it's very sluggish and slow in execution. Does anyone have any idea how to improve the ...
12
votes
0answers
232 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 ...
4
votes
3answers
143 views

Brute force looping & formatting Or Create Union range & Format? Which is efficient and when?

May be my question is not up to the standard for Code Review, but of upmost importance for reassessing methods used in my VBA coding . While preparing/testing answer for a SO post (thought to simplest ...