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
29 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
49 views

Flexible Binary Search

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

VBA - For Each File with extention “.dwg” preform commands [on hold]

i cant seem to get my loops to work right in VBA hoping im missing something simple. Im trying to index thru a specific folder and has tons of files in it and for each file that ends with ".dwg" i ...
0
votes
0answers
22 views

Performance of ID's checking between Access and Excel

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

Excel VBA Sub Routine is extremely Slow deleting a Range

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
87 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
66 views

Using arrays instead of regular loops

I have a working code and it works fine for small amounts of data ...
2
votes
0answers
36 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
87 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
184 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
148 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
247 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
150 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
31 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
115 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
69 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
48 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
72 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
45 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
90 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
79 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
62 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
207 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 ...
3
votes
3answers
118 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
82 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
145 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
64 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 ...
0
votes
1answer
47 views

loop code through each slicer value selection

I have a PivotTable with a slicer that has 5 filters. I'm looking to have these filters to each be selected individually one by one and run a copy/paste code for every filter selected. I've set up ...
2
votes
2answers
76 views

VBA root finder algorithm

I did two simple functions in VBA (see below). When a user calls TirNoPer360 in Excel he must select cashflows and dates (as range), then he will get a rate (...
2
votes
0answers
69 views

Code to email past-due accounts

I am using this syntax to query a table and return a record set and if the number of times total the customer has been emailed is less than 5, send an email with a canned response. If the number of ...
2
votes
1answer
50 views

Store subranges from contiguous range into array based on criteria [closed]

My code parses through a contiguous range of data in a spreadsheet (orng) and creates subranges from orng that contain the same string value (lot number) in the 6th column of orng. orng has 42 rows ...
0
votes
1answer
49 views

Generate an ID number for each new row of text that is pasted into the worksheet

This code checks if "B2" and so on is filled in and if it is then it will generate a new ID number for the row of data that was transferred to the worksheet on the next empty row, I know this code ...
3
votes
1answer
50 views

ERP-system with UserForm that transfers data to next available row in an archive spreadsheet

I made this huge code (Edit: huge for such a simple task) for transferring data and adding an ID number to every new row of data. The only functionality flaw I see is that it creates new ID numbers if ...
4
votes
1answer
94 views

Windows API Declarations For Compatibility

This is an extension of my Reusable Progress Indicator post. I realised that I needed to add additional compiler constants for compatibility of the Windows API functions used in the ...
4
votes
2answers
63 views

Copy Multiple Excel Tables to VBA Dictionary

I have a number of Excel tables that I want to populate into VBA dictionaries. Most of the code is repetitive but there are some unique things for each table that prevent me from making one general ...
1
vote
0answers
33 views

Pulling substrings from a CSV string without knowing position

My task is to copy data from an Excel sheet into charts on PowerPoint slides. To minimize the interactions between the two, I have an Excel macro insert the data for each slide into that slide's Notes ...
5
votes
2answers
164 views

Very Simple Spell (Number to Words) 001 to 999 in VBA

I have come across many spell number functions and have reviewed many VBA code available on the net. The core function that gets called repeatedly is the function that converts numbers from 001 to ...
4
votes
0answers
81 views

Reusable Progress Indicator

As many have done on this site, I decided to create a progress indicator using a modeless Userform. However, my goal was to make said progress indicator as generic/reusable as possible. While doing ...
0
votes
0answers
27 views

Filter by Date, summarise and paste data between spreadsheets

I have written the below working Code to speed up the summarisation of transaction data into a reduced format whilst also checking and providing feedback where there is no data to transfer. Overview: ...
2
votes
1answer
56 views

Loop to copy values from one sheet and paste the values 68 times in another, time efficiency

I have created a large VBA program to automate creation of a data table that is needed to run slicers in an Excel file. While the loop works well in creating what I need, the main loop take an hour to ...
1
vote
1answer
54 views

Bookkeeping Coding - Check and Create Tabs, Copy filtered Data in Loop

As said, this is take 2 (see linked for Take 1 for my massively beginner code: https://codereview.stackexchange.com/questions/223236/) As an overview: I manage the bookkeeping for 40+ companies in ...
1
vote
0answers
59 views

Speeding up formatting of all comments in a worksheet

I have recently added a subroutine, CleanUpComments, to be called from multiple other subroutines to clean up comment positions and sizes. It works, but it is extremely slow taking about 10 seconds ...
2
votes
3answers
77 views

Faster output to Sheet from a collection of Dictionaries

My code is currently offloading parsed data into multiple spreadsheets, but it's pretty slow: about 110k rows and 35 columns in 3.5 minutes. I was wondering if there were a more efficient way of ...
2
votes
2answers
74 views

Bookkeeping Coding - Check and Create Tabs, Copy filtered Data in Loop - EDIT new/better code posted as new thread

EDIT: Improved/More Streamlined Code (rather than spaghetti below) now posted here: Bookkeeping Coding - Check and Create Tabs, Copy filtered Data in Loop I'm a relative newbie to coding and have "...
3
votes
0answers
86 views

Rich data types - boolean object

Context You can probably skim most of this, it's unlikely to be useful for a review and is just for background info. Also download the files I've tried to create a ...
3
votes
2answers
49 views

I'm reading all lines of another sheet and I paste the value that I want to the active sheet VBA

I have a program that read thousands of lines and return the value that I need. The problem is that request take a lot of time probably like 1min just to search and paste the value, even when I'm ...
3
votes
1answer
131 views

How to efficiently loop through paragraphs and make simple changes with Word VBA (Specially reverse loop to Delete Paragraphs)

This is regarding my answer with SO post How to remove paragraph marks with different format in MS-Word. My primary question, is there any way performance of the code could be improved to operate on ...