Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
0answers
33 views

Looping through table rows and replacing key terms in a word template

Here is my code for a sub which does the following: read header of a table into an array reads first row of said table into a 2nd array loops through the first array and replaces the marked ...
1
vote
0answers
31 views

Recursive File Search Using the Dir() Function

There are plenty of examples of using the FileSystemObject to recursively search for a file in subfolders. I thought that it would be interesting to write one using the Dir() function. I was ...
1
vote
1answer
45 views

Improving speed of data clean process in vba

My code does exactly what I want it to. However, being relatively new to VBA, I feel it could be a lot more efficient - namely I think I have overused loops and worksheet functions which are slowing ...
-1
votes
0answers
22 views

VBA Outlook/Excel Object model [on hold]

While coding, i've noticed a quite funny thing which, unfortunately, I wasn't able to google. That's why raising it here -- maybe you can help me. Below code, copied from MSDocs works fine. ...
1
vote
0answers
32 views

Running an UPDATE SQL QUERY within VBA using DAO

I have a macro in Excel that updates a recordset using WHERE a primary key in MS Access matches a primary key in a UserForm label value. The macro is called from a ...
2
votes
1answer
44 views

VBA - If value in sheet1 found in sheet2, then delete data from sheet2

I have 2 sheets setup: Exclusions and Issues Issues has a list of CASE ID's and Columns that list the "Issue" Exclusions will be populated with CASE ID's that are to be excluded (and removed) from ...
1
vote
1answer
58 views

Merging records from one spreadsheet into another

I have created the code below that compare two spreadsheets . It initially compares the range of value from sheet1 ("Status") to sheet2 ("Interface") . Whenever a range matches with the ranges present ...
-1
votes
0answers
34 views

for each vs for [closed]

Best way to loop through cells : for or for each cls in rng ? Sub for() For i = a To b For j = c To d 'things to cells(i,j) Next j Next i End Sub ...
1
vote
0answers
53 views

Copy and Transpose Macro VBA

I have a macro where I open several text files to copy and transpose several columns into my worksheet. As it is now this actually works. I am right now opening the text file, copying a column, going ...
-1
votes
1answer
37 views

How to return cell values of a non-sequential set of cells from multiple closed spreadsheets? Code condensing/simplification [closed]

The code below does what I need it too but it is ugly and clunky. If I could set the list of cells I want to retreive data from as an array that is declared somewhere, then I could loop through that ...
1
vote
1answer
78 views

VBA Function Error Bubbling

What will be the best practice to create an efficient error handler in object class in VBA? Creating some for Outlook (used later in both, Excel and Access) and trying to get the most efficient and ...
3
votes
0answers
73 views

Comparing times of two worksheets

The below code works however it takes several minutes to run. Is there a way to optimise performance so that it can be completed in a shorter amount of time? My code is designed to look at the "...
0
votes
0answers
82 views

VBA processing multiple files

...
2
votes
3answers
100 views

Looking for all numbers where each digit is bigger than the previous digit

This is the "Increasing Number problem", where we are looking for all numbers with \$n\$ digits where each digit is bigger than the digit before it. Or in Mathematical therms where: $$\mathit{...
7
votes
3answers
142 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
45 views

Worksheetfunction.Vlookup value on error

I have this function (wb is a public workbook variable): ...
2
votes
0answers
44 views

VBA Array Functions: insert element, remove element

An extension to array functions I am building for my snake game. This one allows you to remove / insert particular elements. API CALLS ...
5
votes
1answer
71 views

Public Function to remove empty or “” elements from a single dimension array

I've written a function to remove empty or "" value elements from an array or in other words return an array consisting only of ...
2
votes
1answer
107 views

Sluggish Loop Transferring Dictionary Data to Spreadsheet

I've added a timer to my code and the bottleneck comes when I'm looping through 47 rows and inputting data from a dictionary that I previously loaded with values. Since i use these files for a lot of ...
3
votes
0answers
40 views

Stop flickering / refactor code for Excel ScreenUpdating false on Copy Paste to another worksheet [migrated]

I am beginner and still learn about programming MS Excel VBA macros. I need help from the community to solve my problem with macro code on excel. ...
1
vote
1answer
58 views

Loop whose endpoint (?) change during the loop [closed]

I have a loop which insert rows on specific cases. The loop goes through ~2000 rows and insert about 500. Due to the inserted rows it doesn't loop the full range I want. I have solved that problem by ...
2
votes
0answers
85 views

VBA array functions: push, pop, shift, unshift

I want to write my snake game procedurally, using as much windows call as I can so as to practice. Looking into GetAsyncKeyState to capture keyboard inputs and play sound functions. Also making a ...
-1
votes
1answer
45 views

Standardize and reduce repeating same procedures in my coding Excel VBA [closed]

I am aware that my VBA code can be improved.I would like to standardize and reduce repeating same procedures. Can I have some guide lines for this? Where to start? I would be glad to have some ...
3
votes
1answer
56 views

Array that stores values from two ranges and compares if one cell is blank

This is my first Array in VBA for Excel, but I need some help to optimize the code and try to reduce the number of If statements. The long and short of the code is ...
1
vote
1answer
42 views

Vba to create a new column and insert array formula

My below code is the slowest sub in my workbook. I can't set it to put the Array in immediatley, because the mode is calculated incorrectly (calculates for just one ...
4
votes
4answers
170 views

A Word macro that processes trainees' dictation, highlighting errors and checking accuracy

I work in the training department of a captioning agency, where I spend a lot of time grading trainee's dictation transcripts. We receive the transcripts of trainee's dictation of test audios and ...
1
vote
1answer
87 views

Optimizing VBA Select Code

I'm trying to reword the code below to avoid using the Select and Selection terms. I tried to Range("S"...).Copy instead of .Select but that didn't work for some reason. Does anyone know a way I can ...
0
votes
0answers
50 views

Data Scraping Using VBA

I have below code, which fetch data from a Intranet. But its taking more time to fetch data.can someone help me to modify the code to increase the performance. Thanks In Advance Note-I haven't posted ...
5
votes
1answer
59 views

Define a range based on passed arguments

TL;DR I'm self teaching VBA, learning about functions and passing arguments from a subroutine into the function and want to know if my very basic function to define a range is optimal and follows ...
6
votes
1answer
109 views

Snake Game - within worksheet - cells as pixels

Since my rather mediocre attempt at making a Space Invaders game, I stumbled on a cache of Visual Basic for Applications games written by Japanese excel wizards. I have even seen someone create Zelda?!...
2
votes
2answers
71 views

Abstracting and unit testing lookups in Excel table

Background I have a vba solution that I use to ingest investment text reports and reformat them for analysis in Excel. It works, but the macros involve a lot of direct manipulation of Excel objects, ...
3
votes
0answers
49 views

Code runs slower when executed from ribbon compared to directly in module

We use third party addin that downloads market data from the internet into a workbook. The addin works with custom functions that begin with CompanyAddin. For instance, a formula in a cell could look ...
1
vote
0answers
57 views

Further dive into filtering using IPredicate

Preface This is a follow up to my previous post from not long time ago about containers and filtering. This post can be found here. Now, I will attempt to expand what I have learnt from the answer ...
6
votes
1answer
110 views

Class containers, filtering and coding practices

Context Usually, when I have to deal with a collection of items (especially objects) I tent to create a container class for that collection (wrapper?). What I mean by that is: when I have to handle ...
2
votes
0answers
117 views

Fixing Medical Claim Files through Text File Read/Write v2

This is another review on a program I've asked about before, with a different target in mind. Program Function As before, this program makes emergency changes to medical claim files, delivers all ...
3
votes
1answer
61 views

Cell Gradient Fill Macro

I have two functions here, each one displays the gradient slightly differently with up to 5 gradients. Function 1: ...
7
votes
0answers
93 views

Free body diagram generator using MVP for VBA

Based on my understanding of Model View Presenter (MVP) I have it generate a diagram. Is this a correct implementation of MVP? What deficiencies are there in my implementation? UI Generated diagram ...
0
votes
1answer
43 views

Merge info from two sheets info one list

I've created a code that works but takes time to run. Is there any way of making this code work in a more efficient way? In short terms I want to: make a new copy of sheet 1 and 2 Select the row ...
0
votes
0answers
31 views

Weekly TextToColumns formatting, sorting, refreshing all pivots then copy/pasting specific rows to the bottoms of a series of specific columns

Firstly, this code works, but it is long and needlessly inefficient. I want to clean up the code. I am working on a spreadsheet (this code is part of) that I import two other weekly updated ...
2
votes
0answers
85 views

Feels like I have no clue what I'm doing in my “project” - “GAME” [closed]

Never had any programming course. Am not programmer. Does not plan to be. If anyone has a lot a time to lose, I feel like I have no clue what I'm doing / that a lot of my code is very badly ...
1
vote
1answer
36 views

Shortening Custom Document Property additions [closed]

Context I have blocks of if statements that check if a certain cell contains a particular string and to add to the counter of the respective custom property. Originally I was using global variables, ...
1
vote
0answers
31 views

Stack layered lookup

This is code I wrote to check a product structure from 1 table in a database. It's based on a type which can have area(s) and area(s) can have group(s). I have: ...
6
votes
1answer
86 views

Enumerating processes without enumerating flaws of the API

This arose out of the incident where it was realized that WMI could not be expected to be reliable across all various computers. The next thing I looked at was API which was positively awful to work ...
7
votes
3answers
176 views

Standardized and automated Class block generation for VBA

I recently developed a tool which automaticaly generates VBA code in order to smoothly create class templates. I wanted to share this tool with the community, hopefully this is the right place. The ...
1
vote
1answer
74 views

Generating a data validation list and sorting it alphabetically

The following code clears 11 columns that contain lists that are used for data validation on another worksheet. This list is constantly changing due to records being added/removed from the source data....
3
votes
1answer
52 views

User Confirmation using a Userform Modeless and DoEvents

Can this be done more efficiently? The code does the following: Do Stuff Show UserForm and ask user to confirm the shown data is ok User can manipulate and controll excel as usual As soon as user ...
2
votes
1answer
108 views

Making a catalog of pictures

Is there a way to make this code run faster? I have over 6000 pictures in .jpg optimized to a maximum of 40 kB. It starts quickly but after like 2000 pictures it runs way slower. The final .xlsm file ...
2
votes
3answers
86 views

VBA code slowing workbook

I have a spreadsheet with five sheets all of which run the same VBA code; everything was good when it was on just the one sheet, but now it's running on all five sheets Excel is extremely slow to load ...
1
vote
1answer
37 views

Excel custom cell style remover

When you only want a couple styles from a workbook but not all of them it's easier to merge styles then remove those you don't want. How can I improve the form or the backing code? Userform with ...
3
votes
4answers
160 views

Fastest method of getting the bounding box containing all value ranges

I want to find the fastest method of getting the range object which contains all values in a worksheet, while only containing 1 area. Traditionally one might think this is what ...