Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

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.

-4
votes
0answers
28 views

Web scraping VBA with log in with user pop up [closed]

Good day, I am automating a report for my department which currently requires going to a website, putting in your user name/password, then navigating to the proper page and copy the data from there. ...
3
votes
0answers
69 views

Sudoku-lite challenge

You can find the problem here. The example: Column A, B, and C are each 3 and together equal 9: ...
1
vote
0answers
38 views

Executing arbitrary SQL statements using VBA in Excel

I built a workbook to help me extract data from a MSSQL database into Excel. I realize that this is fairly easy to do with Microsoft SQL Server Management Studio, but I can't easily get that installed ...
0
votes
0answers
46 views

Excel macro advanced combine rows [on hold]

Here is my example file for which you can use F8 to step through and see my issue. Column F:G shows my desired output, otherwise here are pictures showing it. As explained in the album I want my ...
4
votes
2answers
61 views

Macro to convert an indented hierarchy to a database format

Our business intelligence suite outputs data as an indented hierarchy, as below: ...
0
votes
1answer
81 views

Generating output comment for 70000 rows

For traversing 70,000 rows (using a for loop), my macro is taking a lot of time to give output comment as per various conditions (...
3
votes
1answer
91 views

VBA script too slow - Checks for duplicate school enrollments and keeps more recent enrollment

I have over 260k lines of data. This code is likely to take more than 30hrs to run. Would like help to speed this up. Or is there a way to use something else like PowerQuery to accomplish this? I am ...
0
votes
1answer
35 views

Pasting from another workbook

I have a macro comprised of this and similar Subs. They work, but I feel like it's slowing it down. Two workbooks are open, and various ranges are copied from one that is hidden (wbSource) and pasted ...
4
votes
2answers
53 views

Importing files into Excel

Any tips to reduce lines, improve speed, or any cool thing, are welcome. I have been using this for years and I just realized, why not improve ...? ...
4
votes
2answers
121 views

Dijkstra's algorithm implemented in Excel VBA

I wanted to implement Dijkstra's Algorithm in an Excel VBA Add-In and built it to be used as follows: Define a list of paths with distances between points. This list needs to contain 3 headings that ...
-1
votes
1answer
41 views

Macro to clear cells that do not appear in a master list

The macro below clears cells that do not match any cell in comparison to a range (a master list). It works on small file but is too slow to work for files with large ranges. ...
0
votes
1answer
67 views

Macro that combines data from multiple worksheets

I have created a macro to combine data from different excel sheets into one final report. As it is obvious from the code provided below, there are very clear steps in the code. The code has no issues (...
0
votes
2answers
107 views

Matching a checkbox caption to a cell's value

There are 30 checkboxes that need to have their caption compared to a cell value. The basic code is repeated 30 times for 30 different check boxes The only Lines that Change per CheckBox are the ...
-1
votes
3answers
121 views

Writing many cells to sheet causes Excel to not respond, replacing vlookups [closed]

I have found that my code causes my Excel to 'not respond' when it gets to this for loop, For thisRow = 2 To lastWSrow(outWS). I'm guessing this is because I am ...
1
vote
2answers
53 views

Order Spreadsheet code cleaning project

I am working on a bit of a multiple part project, the goal of the project is to have a spreadsheet full of order data (order number, customer data, products ordered, prices etc) merged so that the ...
0
votes
0answers
30 views

Collating data from multiple worksheets, conditional

I'm currently creating a 'master' tasks sheet for work, that can draw uncompleted (pending or overdue) tasks from multiple worksheets (upto 10 different projects) and sort through the data. The most ...
1
vote
1answer
51 views

Store account numbers as text

I wrote this code to make sure account numbers (accounting), which are numbers, are stored as text in the range. Replacing r.value by ...
2
votes
1answer
47 views

Getting rid of Merged cells

Merged cells are a PITA in Excel. They prevent doing a lot of actions and make copying columns impossible. So I wrote this little code to change all the merged cells in a given sheet into 'Center ...
4
votes
2answers
100 views

Excel 2016 VBA - Show/Hide multiple shape groups [closed]

I have the following code I'm using to show/hide multiple shape groups in Excel 2016 using VBA and macros: ...
5
votes
2answers
428 views

Hide Excel Rows faster

I've built a VBA Excel macro that works on a spreadsheet of duplicated files, and displays only groups in which at least one duplication is owned by the current user. The spreadsheet lists file ...
0
votes
1answer
73 views

Mapping between 3 sheets, write to final sheet

I have the following code that causes my excel to 'not respond' however if I step through put a breakpoint at the loop ...
5
votes
3answers
353 views

VBA script that copies data from one workbook to another

I've written a perl script that creates a csv file consolidated of values from multiple files. This file that I've created is then used to update certain rows in a main excel spreadsheet my company ...
-3
votes
1answer
77 views

Calculate quantities in a userform [closed]

This is used on an Excel order form to calculate quantities in a userform for variables based on various criteria on the worksheet. It's too much code for a single Sub, but I'm not sure how I can make ...
5
votes
0answers
77 views

IndexedArray Class: Uses a Dictionary Keys to Index a 2 Dimensional Array of Values

This class encapsulates a 2D Array and a Scripting Dictionary. Values are add or returned from to the Array using a Key and a ColumnIndex, e.g. ...
3
votes
1answer
86 views

Auto filter data from worksheet and create vlookup using a different worksheet

I'm working on a large VBA project for work (around 1000+ lines of vba code) This module is called within another module where I disable everything possible like screen updating, events and setting ...
2
votes
3answers
49 views

Find and replace placeholders in Word with values from Excel

I'm new at this and need assistance with shortening/streamlining the 2 pieces of code that is repeated three times each (this is a shortened version as these pieces of code is repeated many more times)...
6
votes
2answers
48 views

Find select values on one worksheet and copy to a second

In the Calculator worksheet of my spreadsheet, for any appliance type present in a building being studied, users enter a quantity. There are 18 different appliance ...
3
votes
5answers
690 views

Writing an array to a worksheet [closed]

When I run my code, I get an "Excel not responding" error at this sub as I step through the code, more specifically the write portion of this code. Can anyone help ...
0
votes
1answer
122 views

Performing transformations on files [closed]

The code opens some files and does some transformations. I am looking for syntactical help where I can do the same thing I am doing now but speed it up. I can supply some of the calls code if ...
1
vote
0answers
155 views

Function to Return a JSON Like Objects Using VBA Collections and Arrays

My goal is to create a compact function that can create a JSON Like object from JSON string. I want a function with a small footprint that I or anyone who wants to use it, can simply paste into a ...
1
vote
2answers
81 views

Retrieve data from eBird API and create multi-level hierarchy of locations

As you may know, I like birds. The Cornell Lab of Ornithology has a tool called eBird where you can submit records of the birds you identified. I recently learned they have an API that I can query, ...
0
votes
2answers
81 views

Run a query for each value in a column

For each ID found in Column A in the XLS file, run a query to retrieve and save a customer. ...
2
votes
0answers
51 views

MS Office Suite VBA: ShellWait() and PowerShell()

I stumbled across this post on SO, and it had a link to some good code; however the code itself was a bit dated. Since the code has a "do not modify" clause I went back to the basics as noted by the ...
4
votes
1answer
189 views

Index Match with multiple columns using input boxes

I find this script handy for preparing ad-hoc reporting. Using two sets of unique identifiers, it allows you to lookup multiple columns of data with the Index Match function. I welcome any feedback or ...
3
votes
1answer
46 views

Extract data labels from a chart in PowerPoint to Excel

Would like to check if anyone here is able to simplify the following code. What it does: Extract data labels value from a chart in powerpoint to excel. ...
6
votes
0answers
96 views

Bringing The System.Text.StringBuilder Up To Lightning Speed In The VBA

After having to swap the feature rich System.Text.StringBuilder for the very fast but very vanilla A lightning-fast StringBuilder, I decided it was time create a ...
2
votes
1answer
183 views

Print a line of code containing an array, for every element index of that array

This function is called by a sub that feeds it lines that the function needs to print for all the elements in an array. This function retrieves a string, ...
0
votes
3answers
237 views

Transforming tabular view of data in an Excel sheet into a vertical view

I have the following code that works correctly on small data-sets, but times out and crashes on large data sets. The goal of this code is to take a tabular view of data on one sheet and transform it ...
4
votes
1answer
125 views

VBA Text Class, sort of like .NET

This is my first attempt at writing some reusable OOP code. The problem I'm trying to simplify is the confusing (at least to me) structure of VBA string manipulation. Example ...
7
votes
3answers
284 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 ...
4
votes
3answers
113 views

Creating JIRA task from Email in MS Outlook

Every day I get 5 to 30 Appointment emails with almost the same content from which I need to create the Tasks in Atlassian Jira. I wrote a macro to automate this process. I've never written anything ...
3
votes
1answer
74 views

A rather accurate VBA stopwatch

Intro Related, related I'm trying to come up with a neat, accurate way of timing VBA code, as I'm yet to find a method to do this in VBA directly (without plugins etc.) The general idea was to ...
2
votes
1answer
43 views

Adding meals to list on Worksheet, from UserForm

As you may know, this is an ongoing thing 1 2 3 I've created a form that pops up when a user wants to add a new meal to the meal plan. The UI is pretty terrible, but what I'm running into is when ...
4
votes
0answers
57 views

Office CommandBarButton Icon Browser in the VBEditor

Frustrated with the solutions that I found online I decided to write my of Icon Browser. From the onset I decided that my Icon Browser should work from the VBEditor and not show any of the thousands ...
3
votes
2answers
63 views

Case function to sum specific rows

I do not have much experience with VBA, and therefore need the help of this community for the following issue encountered. I used Application.Volatile in my code ...
2
votes
2answers
70 views

Speeding up VBA Code that Sets Pivot Table Filters

I've written out some VBA code that sets various filters onto pivottables. The problem I am experiencing is that it is extremely slow and bogs down my system heavily. I've tried implementing various ...
1
vote
2answers
75 views

Optimizing Macro that Generates File Based off different Source Tabs

This is the "largest" VBA project I've worked on, but perhaps not the most complicated. The meat and potatoes of it basically copies and pastes data from 2 source tabs into 1. A large piece of the ...
3
votes
2answers
56 views

Format 2 Excel sheets then save both sheets as a new workbook

I'm trying to optimize some VBA that I put together. I know that using Select a lot slows down performance quite a bit so I've been trying to cut that out where I can. Is there anything else in my ...
6
votes
1answer
74 views

Pruning logs, burning the remains

After implementing some logging in my system, I got tired of digging through very long files trying to determine where specific runs started/stopped to look for differences, so I began adding a date/...
3
votes
3answers
77 views

Fill in workbook with data from another workbook

Followup to this question I made a macro that will fill in data in a workbook that has 65 (24 are user filled) columns and can go as long as 1500 rows. The macro can import about 1500 rows and 24 ...