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.

2
votes
0answers
14 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
48 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
0answers
31 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
27 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
73 views

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

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 ...
-4
votes
0answers
42 views

Is my “project” folowing good practices [closed]

I'm not a dev, during my slack at work I started trying to learn "programmation" (VBA). To make so I decided to try to make a "game" on excel. As I'm mostly using online resources to learn, I have no ...
1
vote
1answer
30 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
22 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
76 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
152 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
67 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
46 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
73 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
81 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
35 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
153 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 ...
0
votes
1answer
51 views

Excel sheets with employees and dates

I have more than 20 sheets in Excel and one main sheet (all programs with 200 names). Each sheet has a column with names and 24 months (Jan 18 to Dec 18, Jan 19 to Dec 20). Each sheet names is a ...
1
vote
1answer
48 views

Getting totals of each primary key by matching it with another worksheet with a many relationship

I have already resolved several others using Application.Index with Application.WorksheetFunction.Match and reduced time to ...
3
votes
2answers
60 views

Cut and paste rows to new sheet based on different criteria in Column A

I have a "working sheet" that contains 15000+ rows. In column A is an identifier for that row. There are over 20 different identifiers i.e 9W, AM, AV, BG, CY, HJ etc. etc. My current code looks for ...
1
vote
0answers
23 views

VBA Strange Email Hangup [closed]

My VBA Code works on one Workbook but not on the other. In Summary the code: Produces the needed end result in both But it lags about a minute in one long But is otherwise identical Sheet names ...
3
votes
1answer
69 views

Excel VBA: Implementing Box Muller, Zigurrat and Ratio of Uniforms Algorithm

This is a very specific question mixing up stochastic knowledge and VBA skills. So very exciting! I'm trying to compare several methods for generating standard, normally distributed numbers given a ...
2
votes
2answers
88 views

Macro to distribute data within the sheets based on tabs name

I wrote a macro which works, but given the large amount of data, it takes a lot of time. I wonder if there is a way to speed it up. Here is a summary of what it does: In "Summary (All)" tab I have ...
2
votes
1answer
101 views

Data scraping from Internet with Excel-VBA

I have built an Excel application, which gets all the titles of books from Amazon.com, which it is asked to do and scrapes the following data out of them: Book Title Author Price What do you need to ...
1
vote
2answers
85 views

Tab month tracker

The code works; the problem is in the processing. I feel like the code can further improved and I believe the answer is Arrays however, my knowledge is very limited. Here, I loop through ~ 1000 ...
3
votes
0answers
55 views

Retrieving data in multiple formats from Excel with Python

Scenario: I have an excel file that contains some data in the first sheet. That data can be simple strings on a cell or CheckBoxes (marked or not). Data example: ...
0
votes
1answer
64 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
75 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
46 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
100 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
50 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
47 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
69 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
1answer
43 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 ...
8
votes
1answer
284 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
52 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 ...
2
votes
1answer
163 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
50 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
67 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
27 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
55 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
69 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
80 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
81 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
99 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
54 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
47 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
55 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
79 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
348 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
98 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...