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.
2
votes
2answers
8 views
Handling errors with minimal repetition when using Find function
I have a function that essentially searches out a value in a table by looking through the column headers and row headers to locate a data point. The function takes the sheet name, the column header ...
4
votes
3answers
195 views
Deleting rows based on a date
I am deleting rows based on a date.
I am loading the entire sheet into an array and doing the evaluation and building a string containing the rows I want to delete.
...
7
votes
2answers
192 views
Conditionally deleting rows in a worksheet
I have used loops to remove lines that are doing various things, but all are deleting rows where certain criteria is met. Some are deleting less than certain numbers, deleting blanks, removing ...
4
votes
2answers
233 views
Script for removing empty rows in a sheet
I often receive Excel worksheets (with empty rows) which I have to process as a pivot table. Because pivot tables don't allow empty rows, I've made this script. Any feedback is welcomed.
...
-4
votes
1answer
35 views
Two similar tests on different variables [on hold]
I've had a doubt about something.
Let's say I have 4 variables, A1, A2, B1, B2.
If A1 > 0 or B1 > 0, I have to check if A2/B2 is also > 0.
What I have is the following but I'm finding it a bit ...
1
vote
2answers
18 views
Filtering Arrays by values in a specific Column
The following function, as evidenced by its name KeepOrRemoveArrayRowsWhereComparisonIsTrue is probably trying to do too much.
How can I better ...
8
votes
3answers
266 views
If ComparisonIsTrue(thisPost, ComparisonOperator.NotEqualTo, goodCode) Then Me.Answer
This is a follow-on to a global enum for comparison operators. I wanted to pass a logical expression E.G. >= someValue as a parameter to a function, so I made an ...
5
votes
1answer
19 views
(^|)(Get|Total|Aggregated|Summary|Data|Report) - #NamingIsHard
The following is the top-level procedure for aggregating, analysing and reporting my company's submitted business from 2015 - present. Reviewing the entire project is unfeasible, so I am just seeking ...
3
votes
1answer
47 views
Trimming whitespace in Excel cells
I often receive large Excel-sheets in which data has been inserted via copy and paste. Usually a lot of the cells contain whitespaces at the beginning and the end before / after the actual value.
I ...
0
votes
1answer
20 views
Toggle button that will hide/unhide columns within a range
I have the below toggle button sub that works;
...
3
votes
2answers
48 views
Resets Rows to default values if column A is missing a value
I have this Subroutine that works, but I have recently started updating the sheet and adding new columns. I'm finding updating the current macro to be a bit of a chore.
Is there a way to use named ...
3
votes
1answer
47 views
Adding or replacing a sheet in an Excel file
I have the following function and want to simplify the code to make it more readable by eliminating redundant lines:
...
4
votes
1answer
143 views
Excel Retrieving Data from website through Internet Explorer
I took a look at Speed up processing between VBA and IE, but I didn't see anything that addressed the speeding up of the retrieval of information via IE.
Since MS got rid of the stock retrieval ...
4
votes
1answer
69 views
Looping through Files in a Folder
I have this simple looping macro, but I can't seem how to figure out how to make it run faster. I tried including more update = false statements as well as well as ...
11
votes
5answers
594 views
Replace many Outlook email rules with a script
I as well as several of my colleagues have had an ongoing problem where we get bombarded with hundreds if not thousands of emails each day with come from automated services, such as SQL DBmail and ...
3
votes
1answer
36 views
Wrapper for complicated Range manipulation
I'm playing around with a class module to try to wrap up some complicated(ish) Range manipulation in Excel-VBA.
I might have a situation where I know the red range spans my target (the target is to ...
6
votes
1answer
69 views
Retrieves Data From Various Excel Sheets Online, sorts, edits, and Analyzes said Data
This is a routine I wrote to streamline analysis I need to do that originally took 1.5 hours and now takes about 4 minutes total run time.
I'm fairly new to Excel VBA so I welcome all criticism and ...
11
votes
2answers
141 views
Forecast maintenance interval for fleet of aircraft
Being new to VBA, and many years removed from college programming courses in Java, I'm seeking opinions about design, implementation, readability and general better practices using VBA to tackle ...
5
votes
4answers
83 views
Find Duplicate with 2 criteria
My code below find the duplicates based on 2 criteria:
The first criteria is the Name in Column A
The second criteria is the Country in ...
2
votes
3answers
88 views
Most efficient multi-find/replace solution in Excel VBA
I'm looking to improve the runtime and efficiency of my VBA code that performs multiple Replace operations on an Excel Spreadsheet. I started out with the following ...
1
vote
1answer
59 views
Code to analyse text get stuck if too much data
I've made the following VBA script to analyse text recurrence in a huge batch of descriptions.
For a small part of the batch the code run smoothly, but when I include everything it tends to loose ...
1
vote
0answers
62 views
Cycling through 15000+ rows to find/highlight rows with duplicate values in Excel VBA [migrated]
Public Service Announcement:
New Users, please take note of my mistakes
This post only remains up as an example of what NOT to post on this site. Though the question has been heavily ...
2
votes
1answer
51 views
Show one sheet for each field in pivot filter field
Into my workbook I've the db of inventory, in another sheet I've one pvt and in the filter field I put the filed "Card". Now I want create one sheet for each "Card" usually I use this code for this ...
4
votes
1answer
56 views
Function to return a legal name for an Excel range: Follow-up
This Excel VBA function was originally the subject of a previous question on Code Review. This function (Namify()) takes a string as input, modifies it to make it ...
3
votes
1answer
45 views
Converting a number into Hungarian text
I'd like to convert some integer number from a cell between 0 and 1,000,000,0000 into Hungarian text. I've found already a solution, a VBA function.
How can be improved this solution?
...
7
votes
2answers
207 views
Function to return a string legal for a range name
I wrote an Excel VBA function that will take a string argument and return a string that is legal for naming a range. Upon looking through this, if you're wondering what I've been smoking, I should ...
2
votes
3answers
124 views
Copying rows from one workbook to another
This code will copy data from a workbook and copy it to an existing (and open) workbook. At the beginning of the month this copy/paste works very quickly, but as the month goes on and the data also ...
3
votes
2answers
53 views
Data Table Report Class
Most of the VBA I write is to produce tabulated reports from spreadsheet data.
So, here is my attempt at creating a CLS_Data_Report class.
Properties:
a ...
4
votes
1answer
72 views
VBA code for testing efficency
I am currently trying out code for timing efficiency (how fast it runs, basically).
The general consensus is that code with ActiveCell,...
2
votes
2answers
36 views
Outlook VBA-Macro for to check mails concerning recipients and attachments
Every week I've got to send a report to a clerk. I also have to add his deputy in case the clerk isn't available.
For not to forget the second recipient and the attachment I've wrote myself this ...
6
votes
3answers
88 views
Simple Caesar Cipher Function
For no particular reason, I wanted to create a function that would take a string and "encrypt" it via Caesar cipher. This function takes a string and shifts the letters left or right (in the alphabet) ...
6
votes
2answers
234 views
VBA Excel - Conditional Formatting Colour Grab
This code gets the cell's colour regardless of whether it's set from conditional formatting or not. It currently works on 2010 and unsure about older versions of excel.
My question is is there a way ...
4
votes
1answer
47 views
Highlighting and copying spreadsheet rows that match a criterion
I use the code (provided below) to check for certain criteria in a row. In this case, if cell F in worksheet "Swivel" contains "After Dispute For SBU" then that row needs to be highlighted in yellow ...
6
votes
1answer
41 views
A global Enum for comparison operators
This approach was born out of wanting to pass a logical expression (E.G. ">=10000") as a parameter to a function.
So, I built an Enum and a function to use it to evaluate logical expressions.
Is ...
6
votes
1answer
53 views
Looking up ingredients and creating a shopping list
This is my first attempt at refactoring - FindIngredients and I'm wondering where I can improve. The github link in the code directs to the project that still has ...
6
votes
3answers
111 views
Copying data from closed workbooks
I'm an intern in an industrial company in Brazil and it happens that I'm using excel a lot. I just started playing with VBA couple of days ago, and I'm amused of how many things it can do for me!
I ...
4
votes
2answers
54 views
Structure for Multiple Potential Find Errors
I have been writing some code to add to our company's Bill of Materials template Excel file. Every project that we do has its own unique Bill of Materials. I am attempting to make it more dynamic and ...
5
votes
1answer
55 views
Get Worksheet Data Array (Standard Methods)
I'm re-writing my module of Standard Methods. Virtually every project I do begins with grabbing some number of Data Tables and putting them in arrays. So, this is my general "Get Worksheet Data" ...
4
votes
2answers
38 views
Transforming and quantizing some numbers in a spreadsheet
I have two functions which am currently running separately and would like them to run at once.
The cartMaxCalc calculates and populates column ...
7
votes
1answer
102 views
VBA Class to persist and restore Excel Application properties
Very often on Stack Overflow, and even on Code Review, I've seen questions and answers that have code that begins by persisting Excel properties like DisplayAlerts ...
6
votes
1answer
54 views
Create a folder and perform a SaveAs
I have this code that is working as intended. Since I am still learning VBA and coding, I was wondering if anyone could look at this at let me know if there is another way to write this. This is part ...
3
votes
4answers
85 views
A function that implements a query
I have some parameterized queries in Access 2010.
They are often used in VBA functions having the same parameters.
In such cases, I give the query and function the same name.
The following is such a ...
8
votes
2answers
129 views
Report Building (Data Retrieval, Validation, Aggregation, Business Logic, Report Building, Visual Presentation)
This:
Is a data table we get from our financial platform with lots of useful information. For reference, "--" is also the string they use to denote empty values.
This:
Is a spreadsheet I built ...
3
votes
1answer
55 views
Perform loop through process on all sheets at once
I have workbook having multiple sheets and having same data on it. Currently I am using macro that .autofilterand copy paste on respective sheets. I am using looping process like:-
...
3
votes
1answer
29 views
Inserting and Populating Chart with Excel VBA
My Code uses a data set which has been modified using another script Calculating Time Difference and then inserts and populates a Chart with that Data.
Save a Macro enabled WorkBook as Book1.xlsm ...
5
votes
1answer
60 views
ExcelVBA loop exports data from spreadsheet to table within Excel
I want to nest a for loop to cycle through the Range.Value but ("B4") throws me off and this can't be avoided. I was considering using an array to do this but ...
3
votes
1answer
83 views
Refactor of vba function to reduce bloat and repeated code
This takes in an array of data built from a source workbook, builds a set of "flags" based on the data in each row of the array. Then it creates a new finalRng ...
4
votes
1answer
41 views
Calculate Maximum Time Difference Within a Time Period with Max 30 Minutes Difference
For ease of Reference, Sample Data Set which needs to be pasted into Column C from row 20. You can paste the text below into word and then copy from word into Excel with the Cell format set to Custom: ...
2
votes
1answer
43 views
Maintaining row height while inserting
I have a subroutine which re-formats cells after a heading is inserted into the document.
Most cells are 12.75 points high, whereas the headings are 15 points high.
When a heading (single line) is ...
3
votes
4answers
294 views
Deleting rows from a spreadsheet where Column C is blank
I have a script which I use to delete rows from a Excel Sheet, dependent on the Column C cell of that row being blank.
I believe deleting a range might be quicker than deleting individual rows, but ...
