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.
1
vote
0answers
5 views
Any way to make this search quicker in excel?
Any way to make this search quicker in excel? Search and display all instances of a full/partial match in the first worksheet from all the other worksheets in the workbook after running a macro to ...
3
votes
2answers
39 views
Writing an array formula to a large number of cells
I am working on a script to write an array formula to a large number of cells. I have a code that works, but is very slow. Any thoughts on how to speed it up? The end goal is to be able to quickly ...
3
votes
1answer
46 views
Compare two datasets in Excel VBA
I made the following code to take two reports and compares them to show the end user elements which are missing from one of the reports so they can make the adjustments needed.
This is the main ...
4
votes
0answers
14 views
Excel VBA object with validated bounds for an interval
I'm working through a problem I have with Excel VBA. I have two object classes. Class clsLoadStep stores three copies of class clsLoad objects. Class clsLoad simply stores a value and a string stating ...
4
votes
3answers
46 views
Excel 'Big' Data Processing Followup
Previous Question (Initial Level Optimization) : Excel 'big' data processing with vlookups
Code Purpose: Recalculate 25 columns of 500,000 rows each based on new information (provided by a ...
2
votes
1answer
25 views
Structured Set of Dictionaries for Table Lookups
Somewhat recently I encountered an issue where my projects were simply taking to long to run. A lot of my macro work comes down to taking multiple tables (generally formatted in the same way) and ...
8
votes
3answers
87 views
Perf wrapper for Excel VBA
For a while now, I've been using this wrapper to avoid retyping most of it :
...
6
votes
1answer
103 views
Excel 'big' data processing with vlookups
I was given code a while back that was a mess, and have completely rewritten it. However now the code takes .2 seconds per line which for a 500,000 line document is much too long.
Purpose of the ...
3
votes
1answer
25 views
Minimize the number of workers/test rigs, with a capacity constraint, using Excel VBA Solver loop
I created a code that runs 60 optimizations through solver through 4 series of loops (each with 15 'iterations'). The code works great but it is taking FOREVER to run through (over an hour). Each ...
6
votes
3answers
326 views
Importing multiple worksheets
I use the following code to import multiple worksheets from another workbook and do some processing. The importing time is too long. Can anyone suggest a more efficient way to import? Should I be ...
3
votes
0answers
34 views
Excel VBA: Set the background-color & border for a user-defined range of rows
On my work I've got to maintain an Excel-sheet. Documenting the incident-tickets I've dealt with.
For to be able to distinguish between single days easily I mark every second day with a light cyan-...
8
votes
3answers
113 views
Immutable Object class in VBA – Creatable only through constructor and not via “New” keyword
Goals for the class
Create Immutable objects – i.e. only Getters – no Setters
Object creation only possible through a constructor, not via New keyword, to ensure ...
3
votes
1answer
44 views
Import Title / Data header Validation Code
I would like some feed back on the following code.
The Idea is as follows,
I have a control spreadsheet which will hold data headers for a variety of reports. The macro will compare the data headers ...
6
votes
0answers
44 views
Error handling to restart Word from Excel inside a loop
I'm often opening, from Excel, all Word documents inside a folder.
And sometimes during the treatments, Word crash without throwing an error.
So I've added a bit of error handling to restart Word ...
1
vote
1answer
42 views
VBA Macro to generate a report of mean values for each date
My Macro that I wrote is running really slowly and I'm not sure why. The Macro deletes the area, then reproduces one column of locations as a row, deleting any duplicates, then copy's across a column ...
1
vote
0answers
54 views
3
votes
1answer
47 views
Looping through cells and deleting column
A quick description of what I want:
Loop through a particular cell row and it's columns
If any cell contains 0, then delete the entire column
So let's say 4,4 was 0, then delete that column (D)
...
2
votes
1answer
77 views
Sorting efficiently by multiple columns [closed]
I need to sort a large array (5 columns x ~500,000 rows) of string variables by two columns. I have code that works, but it takes an unacceptably long time, particularly because I have to do this on ...
8
votes
0answers
66 views
Find all differences between 2 strings
I pulled this out of my code bucket and dusted it off earlier today in response to a post over on SO that made me cringe. This was originally written to highlight changes in Excel cells in real time ...
6
votes
2answers
47 views
Fixing Medical Claim Files through Text File Read/Write
With thanks to @Mat'sMug and @Comintern for their encouragement, here is a program I wrote to help my team fix medical claim files.
The goal of this program is to make the process of emergency ...
3
votes
2answers
76 views
Applying same formatting to multiple borders in Excel VBA?
Is there a better way to format the cells with borders than what you get when you record a macro? For example, I want to add borders to a cell range. The recorded code is:
...
13
votes
0answers
130 views
Snake in Excel in… VBA?
snake-game is a game where you control a snake in an enclosed square without running into the walls or your own body. The snake grows when you run into whatever item you are supposed to run into.
My ...
1
vote
1answer
36 views
Excel Duplicate File name with different Revisions [closed]
I have an Excel where i have file names in one column with the revision numbers in another column. I need to remove the duplicates(file with higher version should be retained and lowerversion deleted)....
6
votes
2answers
47 views
Create table that lists worksheet visibility
Following my previous question Create a table that lists macros in a workbook or worksheet here's my Sub to determine worksheet visibility in a workbook. This arises from updating code that used ...
0
votes
2answers
72 views
how to optimize vba looping code using variables?
This looping has no problem if I have under 100 rows in the looping table sheet. However, If it go over 100 rows, looping takes a while to filtering and paste value in looping sheet. Is there any way ...
6
votes
1answer
58 views
Create a table that lists macros in a workbook or worksheet
I'm updating an Excel workbook that I've inherited and had to figure out if/when/where Functions/Subs were being called. Ran into instances where in the code I couldn't find it being called and ...
6
votes
1answer
91 views
Excel VBA Explanation/Optimisation Needed
I am a self taught VBA user. I was asked to look at the code from a tool which has stopped working due to a data overflow and "fix" it. I was told the code was 'optimised' so that it took only 2 hours ...
-1
votes
2answers
58 views
Write my code in a better way
I'd like to learn a better way to write the code I wrote below.
It works well and does exactly what I want. I wanted to learn to write it a lot better as this is very much novice coding.
Will re-...
4
votes
0answers
19 views
Word - convert list of images to images
I don't work much in Word VBA so I approached this similar to how I'd approach it in excel, which may not have been optimal. I also didn't see any places for errors to be handled.
This takes a Word ...
9
votes
1answer
129 views
Enumerable Custom Collections in VBA with Dictionary features like Exists
To Collect or Hash
The VBA.Collection has a number of limitations, but it is enumerable and you can refer to items by index or key. But the VBA implementation of a ...
5
votes
1answer
60 views
Mirror and update cells across tabs and forms
From the requests below I've attempted to rewrite my request. I agree completely that it would be easier for our company to provide us with help desk software. Unfortunately, my department has the ...
3
votes
0answers
38 views
Comb Sort Multi-dimensional Array on Key
Previously: Comb Sort in VBA
I've been running two different comb sorts (on arrays) depending on whether I want to sort numbers or strings and I figured I would give using ...
7
votes
1answer
52 views
Get data from user input as workbook or worksheet
I had two different functions for populating a data array. I tried to refactor them using a single argument as to whether or not the user should be selecting a worksheet or just the workbook.
The ...
3
votes
1answer
52 views
Iterate through slicer settings and print to PDF
I have a spreadsheet with seven tables (tbl_1, tbl_2, ..., tbl_7) each controlled by its own slicer. Each slicer has six buttons ...
4
votes
0answers
38 views
Dynamic creation of check box [closed]
This code helps to create check boxes dynamically, as you can see in the image, but it takes more time to create them. It sometimes lags for 5-10 sec and then it creates the check boxes.
Can this ...
12
votes
4answers
894 views
Delete rows in spreadsheet where cells match some patterns
I have vba code that loops through a large number of records and deletes rows based on criteria. The issue at hand is that it takes far too long to run. I have never actually let it finish because it ...
4
votes
1answer
37 views
Extract String From Text (Word)
I need to find a specific string of text from a large source of material. I've just used Excel VBA up to this point, so I don't know the Word objects and I ended up just recording some macros and ...
2
votes
1answer
41 views
Automatically generate a thunderbird importable contacts file for all employees
This is implemented in Ms Access 2003. The Table "People" is a ODBC linked database table on a MSSQL 2008 server. The file generated can be imported into Mozilla Thunderbird contacts to have a contact ...
9
votes
1answer
91 views
Produce a Linear Cut List for Woodworking
I was inspired to write this by a recent review of cutting pipes.
The only thing I'm really iffy about is using the WriteCuts method. I couldn't figure out a way ...
4
votes
1answer
85 views
Handling dialog closure in a VBA user form
I already asked a Code Review question about Working with a new form instance every time.
As far as I have decided, that the most important part of the code is to work with the instance of the form ...
4
votes
1answer
45 views
Populating monthly report with new data each month
This code basically checks a cell for the current date and then fills out the report by finding that date and placing the correct values in the correct places. It works fine I just know that I am ...
1
vote
1answer
75 views
Working with a new form instance every time
I have a form that looks like this:
It is initialized with a Shortcut from the keyboard. It is in a module:
...
7
votes
1answer
86 views
Spreadsheet to calculate how to cut pipes
I have written a piece of code that calculates how to cut pipes based on an order and fixed raw pipe length (in this case 288 feet), but I am not 100% happy with my current code as it counts the pipe ...
5
votes
2answers
60 views
Separating data by a particular cell
I am trying to improve some code I did not write, mostly to make it easier for other people to understand (I found it to be really weirdly written). I attempted to rewrite it, and it does basically ...
3
votes
2answers
66 views
Sum hours worked depending on day type
A while ago I posted this question about finding the Date in Range, where I explained I have a spreadsheet to control my shift hours and was interesting in improving one of my functions.
I got ...
4
votes
1answer
72 views
Else if .value = 1 to 92 code check
I've written code that selects column D and checks it for codes 1 to 92 however I have to write out c4.Value = 1 Or c4.Value = 2 all the way up to c4.Value = 92, is there a quicker way of doing this? ...
6
votes
2answers
390 views
Find cells with specific formatting
I'm currently writing code so that when a red-colored cell appears in a column, a message box will appear letting me know that column has an error. I have written the following method:
...
2
votes
2answers
75 views
Efficient method to sort read-in text file in Excel
I've read in a text file to excel from a database and I've done it in such a way that it filters out unnecessary columns. My approach to filter rows was to use two subroutines and call the 2nd from ...
2
votes
1answer
56 views
Comparing two arrays and printing results as a collection of a class
This is really my first time using a class and I originally wrote it without the class and without refactoring.
This is to take two inputs (csv), place the data in arrays and then compare the arrays. ...
6
votes
1answer
78 views
Detailed Last Row Finder
For a project I created a more detailed last row finder which looks at more than one column.
Could you provide any feed back on the code below.
...
