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
6
votes
0answers
109 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
72 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
143 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 ...
1
vote
0answers
30 views

Modify code to compare two files and link to user-form

Due to the help of the community here I ended up with a working code that serves its purpose. The code itself compares two sheets and then creates a report file with the differences between the sheets....
4
votes
4answers
146 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 ...
-2
votes
1answer
30 views

Working Excel VBA code runs and finishes, but gives “not responding” while waiting, could use a speed boost [closed]

My code below works, but I'm sure could use some help to speed it up. I'm using an adodb connection with sql to get info and put it in a userform. Any suggestions would be appreciated. ...
1
vote
1answer
25 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
100 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
0answers
56 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
42 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
68 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
42 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
76 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
69 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
40 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
59 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
190 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
114 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
76 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
142 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
46 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
68 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
48 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
48 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
84 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
61 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
31 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
159 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
78 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
55 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
76 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
73 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
84 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
48 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
92 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 ...
1
vote
1answer
58 views

Copy-Pasting (kind of) from Excel to an existing Word table

What I'm trying to do here is to open an Excel file and then search for and store the data I need (i.e if there is a reference then I copy 2 columns). Afterwards, I paste or write that data into a ...
2
votes
1answer
59 views

Excel - Visual Basic For Applications - Mastermind Game

For those who are unfamiliar with the game: https://en.wikipedia.org/wiki/Mastermind_(board_game) A link to the workbook: https://github.com/Evanml2030/Excel-Mastermind Had trouble getting the ...
3
votes
1answer
78 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,...
3
votes
0answers
127 views

Alternative for VLOOKUP/Index-Match in VBA

I am sharing this function I wrote with the community to provide an alternative to slow VLOOKUP and/or Index-...
4
votes
1answer
66 views

Collecting all relevant data from xlsm files

Is there a way to speed this guy up? The code is designed to go into a directory, open up all of the .xlsm files in the folder, and copy specific data into a target file. This code works as is, but it ...
3
votes
1answer
78 views

Most efficient way to loop through files using VBA Excel

I have a procedure for looping through all files in folders and subfolders starting at a folder which the user can select. The user can select both the source and target folder. I'm using Excel VBA ...
2
votes
1answer
74 views

Change case the data inside a column

I created a code that import the data regularly into a selected workbook. The code run well, it takes less than 20 seconds before the data is imported successfully. How does it work? I get a monthly ...
5
votes
0answers
55 views

Search for used Range.NumberFormats in Excel workbook

On an overly abused workbook we've hit the limit and NumberFormats can no longer be added. If we do want to add more we need to consolidate current formats. My code ...
3
votes
0answers
55 views

Show File/Folder Hierachy as Grouped with Indentation

I was intrigued by this question on SO and was able to come up with a reasonably efficient answer, but I'm interested in learning about further optimizations. You can certainly look at my answer to ...