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
5
votes
1answer
76 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
223 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
83 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
53 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
65 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 ...
8
votes
1answer
160 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
118 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
104 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
119 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 ...
1
vote
1answer
48 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
33 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
92 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
44 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
36 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
91 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
215 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
86 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
62 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
97 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
41 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
177 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
53 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
49 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
70 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 ...
3
votes
1answer
124 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
97 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
300 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
86 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
74 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
67 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
98 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
47 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
104 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 ...
4
votes
3answers
66 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
55 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
178 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
73 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 ...
9
votes
1answer
431 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
57 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
286 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
91 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
32 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
73 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
71 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
83 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
186 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
123 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 ...
2
votes
1answer
87 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 ...