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
8
votes
1answer
4k views

Speed up processing between VBA and IE

I am looking to speed up the exchange from vba to IE. The sendkeys works, but I was curious if there were a better way to do this? The site that it routes to is a form, but there is no submit button. ...
8
votes
2answers
925 views

Generating a collection of controls

This question I asked previously mentions a function named BuildControlCollection, which I didn't go into the details of since it wasn't relevant. However, because ...
8
votes
1answer
1k views

Space Invader Style Game Written In VBA

EDIT: Link to the github, https://github.com/Evanml2030/Excel-SpaceInvader THE README (APPLIES HERE TOO): VBA version of a classic game Yes, I know that "Missile" is repeatedly written as "Missle"...
8
votes
1answer
412 views

Logical Predicate Class

For a while now I've been playing around with various ways of passing a logical predicate E.G. >= someValue as an argument to a function. (see: If ...
8
votes
1answer
168 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 ...
8
votes
2answers
2k views

Overriding all Access form control AfterUpdate methods

I have a set of custom event handlers for all my textboxes and combo boxes. The reason for this is my users want the bound form (to SQL Server) to immediately push changes to the recordset, rather ...
8
votes
1answer
149 views

Honey I shrunk the view

Going down the rabbit hole, I wanted to be able to change the size of a form, and not have to worry about the layout of the controls. vba doesn't support user-resizeable forms, but forms still have a ...
8
votes
1answer
256 views

Validate an IBAN

I've found an explanation on Wikipedia on how to validate an IBAN. Since the number generated from the transformation can cause overflow with all data types available in VBA, I've worked with ...
8
votes
1answer
862 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 ...
8
votes
1answer
100 views

Joining collections… because it's fun

I'm working on a LinqEnumerable class (reviewable here), and I'm no Big-O expert and I'm pretty bad with algorithms, so I wonder what the complexity is for what I'...
8
votes
2answers
132 views

Worksheet that lists a person's name

I have a worksheet that lists a person's name (column A; many duplicate names are in this column) with associated data (columns B through G). I have code below that takes this list of a ~ 1000 rows ...
8
votes
1answer
465 views

Growing my own Tree (Structure)

Summary VB6 doesn't have a great selection of data structures to work with, so again I find myself creating my own. I have a need to dynamically generate a directory structure on the file system. The ...
8
votes
1answer
1k views

PropertyComparer Class Implementing IComparer with ArrayList

By implementing the MScorlib IComparer Interface, my PropertyComparer Class enables the sorting Objects added to an ArrayList by the their properties. You can also determine if an Object is in the ...
8
votes
1answer
100 views

You're logged in, except it's an error

I've automated downloading of data from a 3rd party website. The process involves logging into the site, downloading the data, then logging out - lather, rinse, repeat for each client. The code is in ...
8
votes
2answers
344 views

Making a report from payroll details

...
8
votes
1answer
215 views

Access function to return “number of working days until date” for form

I need a function I can put on a form which does: Calculates number of working days until a given date Excludes weekends Excludes any dates in a "holiday" table This is replacing something which ...
8
votes
1answer
403 views

A hacked-up testing framework

Inspired by this post, I wanted to be able, in any vba project I could be working on, to create a test class and write test methods. Like this: ThisWorkbook Workbook | class module (client code) <...
8
votes
2answers
300 views

Battleship UI: GameSheet

In the previous post I presented the MVC architecture, but all we saw of the View was the pretty bits. From the outside everything looks innocently pretty: The guts of the worksheet's code-behind is ...
8
votes
1answer
290 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 ...
8
votes
1answer
1k views

Delete rows from ListObject based on one or more criteria in a column while maintaining original sort order

The title is fairly self-explanatory re: my goals, though I'll add that speed/efficiency is a priority. Originally, I tried using an autofilter on the ListObject and deleting all visible rows. But ...
8
votes
0answers
277 views

Private VBA Class Initializer called from Factory #2

About This is a follow-up of a previous question Private VBA Class Initializer called from Factory. I've decided to create a new question instead of answering my old question because I would like the ...
7
votes
3answers
331 views

Script to hide Excel rows where certain columns contain 0

I have the following script which works, but takes a lot of time to run on a worksheet with 2000+ rows. Anyone know of a way to speed it up? The code runs through the workbook and ignores the pages I ...
7
votes
4answers
4k views

Extracting key information from a directory of XML files

I have written some VBA to loop through a directory of 15,000 XML files and extract key information from these files into Excel - this is unbearably slow. Can someone please assist me in optimising ...
7
votes
2answers
189 views

Searching for an empty row in a given destination

It simply searches for an empty row in a given destination. If it is empty, it performs certain actions (copying, pasting, etc.). If it is not empty, it goes to another row. I managed to do it with ...
7
votes
2answers
4k views

VBA Function slower than Excel Formula

I've replaced the following horrendous Excel formula (values and names have been replaced): ...
7
votes
3answers
4k views

Class Module to wrap up classic ADO call to SQL-server

I do a lot of copying excel vba code related to classic ADO from workbook to workbook, so I've decided to add the code to a Class Module in a single utility workbook and then in all other books just ...
7
votes
3answers
867 views

Extracting data from CSV file on the internet

This is my code I made that extracts data from a .csv file stored on the web. My sheet called 'New Data' stores this data for 2 days. I store 2 days worth of data so that when I run it next time, it ...
7
votes
4answers
11k views

Hex encoding and decoding of ASCII strings in VBA

I needed to convert some Ascii text to binary in Hex format 0x00FF.... I've written an EncodeHex and a ...
7
votes
3answers
314 views

Summarize raw data in new sheet, 2.0

Thanks to zak for some great advice on this previous question. This is a follow up question: I have an Excel workbook with two sheets: "Raw data" and "Summary". In the raw data sheet there are ...
7
votes
3answers
591 views

Conditionally copy columns

I have this Excel/VBA code and here is what it does: I have 3 sheets, where first sheet has two columns that will be used (A,C). 2nd Sheet is just the raw data file that will be imported from ...
7
votes
2answers
3k views

Concatenate disjoint Excel ranges to use as a single range in a formula

I need to vertically concatenate two disjoint ranges in VBA to be passed to an Excel function (in this case the LINEST function). I hate working with arrays in VBA ...
7
votes
3answers
3k views

Retrieving data from files in the folder

I am to use VBA code to simply retrieve certain data from all Excel files in certain folder and paste it in working spreadsheet. I am not sure that this is the most efficient (which matters as I would ...
7
votes
2answers
3k views

VBA Text Array - Scan two columns rather than one

I have some code which is designed to scan columns F and G for occurrences of words found in an array, the array containing text ...
7
votes
3answers
2k views

Retrieve value from a formula in cells into different merged cells and retrieved value can be edited

I have created this macro and having a small issue. I am very new to this VBA and did this with experts’ guides by googling it. I am creating an excel form for a work purpose at work. The Excel form ...
7
votes
3answers
366 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 ...
7
votes
2answers
499 views

Select and Paste Rows in a Different Location

I am working with the output from a chemistry instrument, in .xls format. The output contains up to seven blocks of data corresponding to different types of samples e.g. controls, unknowns, ...
7
votes
2answers
8k views

Traversing and printing complex Dictionary types (Scripting.Dictionary) - Dictionary inside of a Dictionary

Based on this SO post - also reposted on vba4all.com with a few more details and explanations. Please notice there currently is no error handling whatsoever as I didn't analyse and consider any traps ...
7
votes
3answers
7k views

Progress Bar Form

Forms in MS Access are really just special glorified classes, so you can create new instances of them from code like this. ...
7
votes
1answer
408 views

Comparing tables and printing discrepancies

Here's my first VBA project. I have some very limited Python experience, and this is my first VBA project. I'm sure I could have done it a lot more simply, but I just stuck with what I knew, and ...
7
votes
2answers
1k views

Manipulating the global address book in Outlook

I am using VBA to manipulate the global address book in Outlook. My method takes a contact and returns a complete list of everyone who reports through that person based on the Outlook org structure. ...
7
votes
2answers
105 views

VBA macro - searches through a filtered table, stops when entry passes criteria

I have written several subs which are then called from a main sub. Individual subs run very quickly, most are instantaneous (the DoFind sub takes a few seconds to run due to the large amounts of data ...
7
votes
2answers
989 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 ...
7
votes
3answers
60 views

Re-worked Workbook_Open, Creating DateStrings

So, I had a go at re-writing my Workbook_Open event based on The advice from my previous question. Thoughts? ...
7
votes
2answers
5k views

VBA macro which filters and splits dataset into new workbooks

This VBA macro uses a worksheet (source_data_worksheet) to filter and split out the records based on about a dozen unique column (D) values in a dataset of about to separate workbooks which are saved ...
7
votes
1answer
707 views

A more readable InStr: StringContains

Consider the following: If myString = "abc" Or myString = "def" [...] Or myString = "xyz" Then In C# when myString == "abc" ...
7
votes
2answers
476 views

Excel display driver class for Tetris

This is the first of (hopefully) several posts as I reimplement an ancient (and unfortunately long lost) Excel VBA Tetris clone. The main workhorse class in the game engine is the display driver, ...
7
votes
2answers
203 views

Reusable VBA macro to prevent Excel from destroying the data (+ backup capacity)

My goal is to prepare the Excel file (see the download link at the end) I would share with my non-technical colleagues, so that they enter some data into it and then I was able to automatically ...
7
votes
2answers
104 views

Adding all IDs associated with a Fax to one Row

I found a custom function called UniqueItems() to use and then developed my own RemoveDups() and ...
7
votes
2answers
3k views

VBA ClickBot featuring AJAX waiting and Element searching

If you work with a database that you only have access through a web interface, this one is for you. You probably don't have access to any programming languages other than VBA through MS Office, which ...
7
votes
1answer
952 views

Macro for inserting a mini table between heading styles

I am using the following macro to insert a mini table of contents between two identical heading styles (e.g. Heading 1). It works fine on small documents, but is very slow on large documents. ...

1 2 3
4
5
25