Stack Exchange Network

Stack Exchange network consists of 174 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.

0
votes
1answer
46 views

Opening a workbook and copying pages to master workbook

I have this code which opens 2 workbooks, copies a sheet and paste then to the master workbook. It is currently taking 3 minutes. Can this be done quicker (i.e. without opening each workbook to copy)?...
-1
votes
2answers
53 views

Excel Range less clumsy [closed]

As an old dog (age 73) learning new (Excel VBA) tricks, I am reasonably happy with putting together the code below. But I think it could be cleaner. How would you have coded it? ...
3
votes
1answer
38 views

Superscript all numeric characters after ^ in an excel cell

I have material property entries in a cell those look something like this: cell.value="O2 Permeability [cm^3/m^2]" The goal ...
4
votes
5answers
93 views

Function for minimum non-zero with multiple additional conditions

This function works, but seems unnecessarily complicated. I would like to simplify but performance is vital as it is called many times. The conditions are: 1) If either value is negative, then ...
1
vote
3answers
42 views

Macro to remove rows with data not meeting certain requirements

I have created several macros that delete rows based on cell values. I run it 5 times for different data, but think there should be a way to combine them and speed up the macro. Currently, it is ...
2
votes
0answers
39 views

Excel VBA animation runs slowly after editing worksheet

My VBA macro displays an animation based on a row-by-row time step simulation in the worksheet. The user enters the desired inputs and the worksheet calculates the evolution of the simulation in a ...
3
votes
0answers
57 views

Document “Search Engine” in Excel

I've created a somewhat "Brute-force search engine" based on a document's filename, as well as an additional keyword that describes the said document. I had to make one as my company's resources are a ...
0
votes
0answers
27 views

MS Word VBA copy, select and paste - best practices

Perhaps unwisely I've attempted to answer this question. This was literally a 5-minute job, I've Googled what I needed to know as I've never done any MS Word VBA before. What improvements would you ...
0
votes
1answer
34 views

Loop each folder and subfolder to get values

Any tips or improvement to speed my code? I'm looping each file in the specify folder and subfolders. I will open each file and get the values I need and copy it to my activesheet. It is taking quit ...
0
votes
0answers
25 views

How could I make my code faster (copying columns and auto filter)

I'm new to vba and wrote the code below to match and copy columns. Here is a breakdown of what it does. There are two main worksheets. Worksheet1 has the columns of which useful to me are "brand" ...
8
votes
1answer
220 views

A simple ascii progress bar

I've been wanting to experiment with a bit of error handling and robustness to make my code more user friendly. I was inspired by this answer on UX to try and add a progress bar to a button - but was ...
0
votes
2answers
47 views

Searching multiple Excel sheets for the cheapest item

I am creating an estimation tool, where users can input part numbers and quantities, and the tool will output the lowest cost for that part number based on searches through multiple databases. I ...
-1
votes
0answers
45 views

How do you loop through hard coded variables in VBA?

Things that needs improvement: 1.) Copy pasting of the same code logic 2.) Clearing the combo box's list using .Clear requires me to store the value into a ...
2
votes
1answer
55 views

OOP Cross Sum Solver

Not satisfied with the Brute Force Solver that I wrote for All possible combinations of 1 to 9 in the same cells without repetition, I created an OOP Cross Sum Solver. As expected my OOP Solver ...
1
vote
1answer
48 views

2D array script for retail

I have a script that takes manager names in Sheets("Mgrs") and extracts the employees that fall under each manager in ...
2
votes
3answers
62 views

“Pivoting” data with VBA

I've attempted to erite some VBA for this this question. The output is OK but the code is not very elegant. I'm happy-ish with creating unique list of names and certificates, but the rest seems ...
1
vote
0answers
25 views

Open/Close several workbooks to sort data

I have written a macro to process all excel files within a user-chosen folder and then save the processed files as a new file in a new folder ("FINAL"). The macro I have works, but is slow. Do you ...
3
votes
0answers
50 views

CheckedRange Class

This class is used to make Excel Cells behave like Checkboxes. Clicking the cell raises a CheckedRange_Clicked(Target as Range) event and toggles it's value ...
2
votes
1answer
68 views

Retrieve data from table with criteria

I have to create a custom table (like a pivot table), where users can find immediately the total of items, and when clicking on data, get the db page correctly ...
1
vote
1answer
73 views

Matching huge data set of business data

I am pretty knew to the VBA world but I am really eager to improve my skills. I have a case where I would like to match cancellation with new business to see what we get back from our dropped business ...
0
votes
2answers
51 views

Speed up array loop vba Excel

I've got here from stackoverflow I have a table with this data: I have this code: ...
3
votes
0answers
89 views

Excel VBA to download from IE Database Stops Responding

first time using Code Review, although I have been active on StackOverflow, hope I'm in the right place. I am putting together Excel VBA code that using information from various columns on a ...
1
vote
1answer
44 views

Excel VBA speed up code to hide rows/delete color

I am using following code to delete color and hide rows that include several criteria before exporting sheets to pdf's. Is there any way to speed up this process as it is taking quite a lot of time to ...
1
vote
1answer
28 views

A function to validate that an unbound form in Access has all required fields filled out

My goal in this code review is to see if i can figure out how to optimize the Validate function takes up fewer lines of code. i know there must be a way to cut it down so that it doesnt span 300 lines ...
1
vote
0answers
51 views

Is it always faster to rewrite a workbook function and store it in the module as a UDF [closed]

I was looking at this answer in SO and I was wondering if it would be faster to write the code for the CountIF function. I imagine it would look something like this:...
2
votes
0answers
75 views

Use arrays to perform 'VLookUp' type activity between workbooks

Background I have the below UDF that opens a workbook (this workbook has extracted data from a queue within another app). I get the used range from this workbook and assign it to an array (...
3
votes
1answer
188 views

Error-Handling Class and Logging for VBA

I've been reusing an error class in Excel VBA projects for a few years now and I'd like to see if there are ways to improve it. Any suggestions for style, code, etc. are all welcome. The 2 ...
3
votes
1answer
89 views

Date input wrapper for TextBox

Purpose I managed to get completely nerd-sniped earlier today by a question over on SO and decided to take a stab at adapting my numeric input wrapper for TextBox...
0
votes
0answers
20 views

Loop each items are the unique solution to retreive one specific property from one collection object?

with this code I add item/properties into my collection: ...
4
votes
0answers
67 views

So what if the “View” in an MVC isn't a Userform, but is the Worksheet?

So I've been following the discussions around VBA as an OOP language and the applicability of various design patterns, such as MVP or MVC. Excellent postings on these topics are extremely informative ...
3
votes
0answers
82 views

Responding to Events of Dynamically added Controls

I was asked for an example of a class to wrap dynamically added controls as I suggested in this Answer. Although it is out of the context of a Code Review, I thought that it would make an interesting ...
2
votes
3answers
83 views

A function that replicates DLookup, and can use parameterized queries

This function was built to replace DLookup so that i could pass in parameterized queries for a speed increase. I also wanted a simple way to get more than one value if needed. For example, if i want 4 ...
1
vote
1answer
53 views

Loop through different controls and enabled the state of the whole group VBA

I got the below code that is looping through all controls and changing the state in a after update event and enables new row for user to use. Everything works fine but i'm just wondering whether there ...
7
votes
0answers
123 views

Web Battleship -The Unofficial Battleship UI

I've written my own UI for Mathieu Guindon's VBA Battleship (Battleship UI: GameSheet) which uses a webpage in a WebBrowser control for the View. Webform: Userform ...
4
votes
2answers
139 views

Space Invader Style Game - Model / Presenter Decoupled From View - Functional Again Too

Thank you for your patience. The game is fully functional again! I have tried to incorporate all the advice I have received so far. Thank you for taking the time to review and comment. I have now ...
1
vote
1answer
55 views

Space Invader game written In VBA, 3rd iteration [closed]

Here is a link to the workbook, classes, modules and forms: https://github.com/Evanml2030/SpaceInvader/tree/master Positive: I have decoupled the view from the control / presenter. I have ...
3
votes
0answers
50 views

Unit testing a custom collection

To enable unit testing in the VBA IDE I'm using https://github.com/rubberduck-vba/Rubberduck. I've created a class and a custom collection class to work with structural members I need to filter. The ...
1
vote
1answer
67 views

User form that searches a spreadsheet

I have this userform that looks up into a sheet. The problem is that when there are a lot of rows to look up it takes too long to execute (for example 100 thousand rows). Is it possible to reduce the ...
2
votes
2answers
111 views

Index Match implementation

The code below takes header variables, retrieves the column index, and then, using an Index/Match function, returns data from a matching account number. The reason it is written this way is because I ...
1
vote
1answer
40 views

Sending data from sheet to other sheet in Excel (from a dispatch note to some records)

I have this code to save data into some records. It is a little slow, so how can I make it faster? To the first record (sheet MOV MERCADERIA) it copies 23 columns ...
3
votes
2answers
129 views

SpaceInvaders Second Iteration - Fully Functional - Next Up New Presenter Class

Going to take a look at Rasta's comment below tonight and just realized I am using byref to pass everything again - DUHOH - Old habits die hard I have revamped with some of Comintern's improvements ...
2
votes
1answer
110 views

A function that tells you if a particular set of data exists in a listbox

I created a function that checks to see if a particular set of data has been added to a listbox and returns a boolean to indicate if the value was found or not. I am asking for critiques on my code. ...
2
votes
2answers
97 views

Automating the creation of a weekly schedule

I'm trying to build an Excel workbook to automate and aid in the creation of a weekly work schedule. My current workbook is functional, but it's slow - especially on one task where I have a list of ...
7
votes
1answer
255 views

Space Invader Style Game Written In VBA

EDIT: Link to the github, https://github.com/Evanml2030/SpaceInvader/tree/master EDIT: SpaceInvaders Second Iteration - Fully Functional - Next Up New Presenter Class ^ a link to second iteration. ...
3
votes
0answers
197 views

Await in… VBA?

Asynchronous programming is tricky enough already, and VBA doesn't make life any easier. I wanted to create some basic functionality to allow async processes to mark completion without having to ...
8
votes
2answers
170 views

Battleship UI: GameSheet

In the previous post I presented the MVC architecture, but all we saw of the View was the pretty bits. From the outside everything looks innocently pretty: The guts of the worksheet's code-behind is ...
5
votes
0answers
167 views

Battleship MVC Architecture

The entry point is the Macros module, which - for now - includes only a single procedure, at a very high abstraction level - I'm quite happy with this: ...
1
vote
1answer
42 views

Copying data from various sources into a master list

I am working on a Master List, where I am copying data from various sources for each month into the columns Z, AC, AF, AI etc. (always separated by 2 columns). Then I copy that cell all the way down ...
1
vote
0answers
43 views

Insert Emojis Into Outlook Email With VBA Application (For those of us who cant use add-ins)

I cannot use add ins here so I set out to create an excel application that could get the job done. I suppose that I could easily transport this into outlook? Basically I want to click one of the ...
1
vote
1answer
36 views

Function to get constants of range

The following small function receives a range and returns it with only the cells that contain constants or Nothing if there's none. ...