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
3
votes
1answer
118 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
84 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
84 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, ...
12
votes
4answers
695 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
109 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
69 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
92 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
82 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
66 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
72 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
69 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
128 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 ...
4
votes
1answer
68 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
113 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
111 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
115 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
65 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
366 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
39 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 ...
12
votes
1answer
416 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 ...
2
votes
0answers
72 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
51 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 ...
5
votes
1answer
159 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
34 views

Performance of ID's checking between Access and Excel

I want to increase the performance of this code: ...
2
votes
1answer
180 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
70 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
118 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
87 views

Using arrays instead of regular loops

I have a working code and it works fine for small amounts of data ...
1
vote
1answer
65 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
133 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
256 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
866 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
293 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
178 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
47 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
151 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 ...
-1
votes
1answer
105 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
68 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
88 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
51 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
156 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
305 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
43 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
68 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 ...
13
votes
0answers
371 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
189 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 ...
3
votes
1answer
95 views

Updating yearly reporting templates, ideally before the next year starts

Work flow is: Copy Previous Year Reports Folder Update Following Templates Save Close There are 19 workbooks in the Source Folder and in the NewYear Folder. Each Workbook has to save the final data ...
3
votes
2answers
153 views

Simple Number to Words using a Single Loop String Triplets in VBA

As an update (conversion) to my article “Simple Number to Words using a Single Loop String Triplets in JavaScript” in JavaScript, I have converted the code to work as a VBA function using the same ...
3
votes
1answer
65 views

Matching and Merging CPU info between worksheets

Goal: Transfer Sheet Test1 2nd Column to Sheet TestS 2nd Column. Where: For each CPU in Test1. If CPU name in Sheet Test1 ...

1 2
3
4 5
24