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.

learn more… | top users | synonyms

4
votes
0answers
61 views

Plotting charts for a large data set

I have created an automated macro which takes vehicle crash data from a .csv file and automatically creates a pivot table and plots and compares it to the previous year. The code is approximately 1400 ...
6
votes
2answers
49 views

End of day receipt emailing process

The following functions/routines are an automation of an end of day receipt emailing process that formerly would take up to an hour, but now takes less than a minute. Things to note: I changed the ...
6
votes
2answers
59 views

Comparing vehicle crash data the previous year

I have created an automated macro which takes vehicle crash data from a .csv file and automatically creates a pivot table and charts and compares it to the previous year. The code is approximately ...
14
votes
3answers
233 views

Modularise Project and Deduplicate Code By Loading External Modules On Startup

Starting point I kind-of inherited a VBA project at work that consists of a bunch of Word document templates that provide users with the tools they need to create documents according to company ...
6
votes
2answers
54 views

Change list separator, parse file, restore list separator to original value

Following up on @Vogel612's friendly advice from the vba-rubberducking chat room, I am posting my working code for open review. Thanks also to @Mat'sMug for the ...
4
votes
2answers
35 views

Pick a folder and consolidate workbooks

I'm trying to create a simple way for current & future (non VBA literate) employees to accomplish some of the tasks I've struggled with. The first being Parsing cells containing Line Feed ...
6
votes
1answer
52 views

Parsing 4 Million Filenames

This is the next step in my project to query Companies House records. The first step, retrieving and validating the company numbers to be targeted, was covered here and I will include that code at the ...
4
votes
1answer
44 views

Validating company registration numbers

N.B. The next stage of this project, which covers parsing filenames and checking numbers against the list produced in this question, is covered here. I have a list of company numbers (UK companies ...
4
votes
2answers
53 views

Parsing cells containing Line Feed Characters

Link to sanitized xls on dropbox if test data is needed Essentially the reports I work with aren't bad - The issue is the way it exports to excel - With the problem being that these cells are ...
3
votes
2answers
360 views

Count rows of a table

I am trying to count the rows of two tables generated by SQL connections in Excel VBA. My plan was to use ListObject.DataBodyRange.Rows.Count however, this errors ...
2
votes
2answers
33 views

Manage InputBox method reference entry

Exploring the VBA Application.InputBox method I've realized that catching user cancellation is tricky when type is reference, so return value is a range object. ...
7
votes
3answers
124 views

Turn logged data into fancy plot

I'm logging data from an embedded platform over UART. This gets saved to a log file and I want to process the saved log with Excel. I have no idea whether what I did was even remotely a good ...
9
votes
2answers
233 views

Extracting Country Names from Cell Values

This fairly clunky looking VBA script successfully pulls names of countries from an Array, but could it be made shorter? Mass of text from Column C onward in each row may contain the names of ...
5
votes
3answers
65 views

Extracting information from spreadsheet into 12 tables (one per month)

I have a file with more than 100k rows, but the structure is simple: ...
3
votes
1answer
43 views

Handling name variants

Part of my standard modules deal with my company's employees and linking them to/from various data sets. The following are a series of enums/functions to handle peoples' names, namely an ...
5
votes
1answer
112 views

VBA Copy Cells Stable

I'm very new to VBA and have put together a macro that currently does exactly what I want: copy cells from one worksheet to another, clear the cells copied, refresh my pivot tables and save the file. ...
4
votes
2answers
159 views

Converting all cells to text

Microsoft Excel cells basically have two values: the formatted value that is shown to the user, and the actual value of the cell. These two values can be extremely different when the cell has certain ...
5
votes
2answers
56 views

Replacing accented letters with regular letters in a spreadsheet

I am using the following VBA code to replace accented letters with regular letters in a spreadsheet. This is necessary because these spreadsheets have to be uploaded to an import tool that does not ...
3
votes
2answers
55 views

Excel VBA highlighting macro

I have this Excel macro I created to highlight all instances of a number if at least one instance is already highlighted before running the macro. ...
6
votes
1answer
135 views

Modularised Document Construction

The goal: A set of configurable options, combined with a standardised data source, to produce a standardised Report. This question: One module of said document, with accompanying code. Input: ...
4
votes
3answers
34 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
227 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. ...
10
votes
2answers
232 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
253 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. ...
1
vote
2answers
24 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
277 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
25 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
60 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
39 views

Toggle button that will hide/unhide columns within a range

I have the below toggle button sub that works; ...
3
votes
2answers
49 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 ...
4
votes
1answer
55 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
174 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
81 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
643 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
40 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
87 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
152 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
99 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
114 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
70 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 ...
4
votes
2answers
68 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
62 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
65 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
219 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
129 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
55 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
73 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
46 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
102 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
272 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 ...