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
26 views
VBA syntax that compiles sub data into master sheet
I am using this syntax to bring in the names of all employees whom are active, then from there filter the data on subsequent worksheets and use a formula to bring the data over to the ...
-4
votes
0answers
13 views
Userform Syntax Error on Simple Command Button Function [on hold]
I am using the following code for a command button to get a userform to go to the previous row and display the data and getting a syntax error:
...
-1
votes
2answers
57 views
Code iterates through all rows and inserts value in cell for each
I would like that this code would now be done in a faster way:
...
3
votes
0answers
40 views
String Repeat function in VBA
VBA has a built-in functions for repeating a single character:
Function String$(Number As Long, Character) As String
...
1
vote
1answer
9 views
Show all full/partial search matches in the first worksheet from other worksheets
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 clear formatting of previous search results.
I ...
3
votes
2answers
59 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
58 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
17 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
51 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
26 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
100 views
Perf wrapper for Excel VBA
Spoiler
This code is actually BAD for performances! So don't use it! ;)
For a while now, I've been using this wrapper to avoid retyping most of it :
...
6
votes
1answer
106 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
28 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
336 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
36 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
121 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
47 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
56 views
4
votes
1answer
53 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
78 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
69 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
48 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
79 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
135 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
37 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
73 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
92 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
60 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
64 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
40 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
53 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? ...
