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.
6
votes
0answers
41 views
2
votes
1answer
38 views
Eliminating empty spreadsheet cells from a range
I am copying data, that is one long list, into an excel sheet and I want to use a macro to organize it visually. The problem is that the data I am copying has empty cells that are unnecessary and ...
2
votes
2answers
35 views
Error Trapping for dialog box reply
How can I improve upon this code to apply logic based on the user's response to a SaveAs Dialog Box 'do you want to overwrite existing file' question:
...
3
votes
1answer
35 views
Writing to many cells from many controls' respective value
I have this program on Excel VBA, that implements a userform in order to simplify input and to create control charts from the data input analyze the data and send emails if certain conditions are met.
...
3
votes
0answers
36 views
Avoiding a call to CommandBarControl.Picture
Earlier today, I encountered an Access Violation error when opening the VBE for SolidWorks 2006, while Rubberduck VBA was initializing.
I traced the Access Violation back to an attempt to set the ...
5
votes
1answer
36 views
HTML tag-adding function for Excel Formatted cells
I have several functions, whose common end goal is to make it possible to write the contents of an Excel cell to Microsoft Access and be able to bring it back from Access as well, all while retaining ...
0
votes
2answers
251 views
Check existence of item in collection [closed]
Checking whether an item exist in a collection isn't straightforward in VBA, it requires error checking and during error handling we need to clear the error and also resume code to be able to ...
2
votes
1answer
31 views
Workbook for tracking projects
This workbook is used to track projects. I have this VBA code linked to a Form Control button. When I press the button it will run through and get information from all the project sheets and feed it ...
-2
votes
0answers
28 views
Excel VBA to edit response in Google sheet via Google form [closed]
I have modified this Excel VBA code to edit response of Google form, this code is working for one url (Response edit URL) but how to edit all existing response with excel until the last empty row. Any ...
5
votes
2answers
255 views
Summarizing data from multiple sheets
I have 9 sheets with 1668 rows and 34 cols. The code collects all data from every sheet into one Summary sheet in the following format:
...
8
votes
1answer
59 views
Functional Framework
First Class Functions
So, VBA doesn't support functions as first class objects - no passing functions as arguments or storing them in variables.
I eventually found a way to implement function ...
3
votes
2answers
44 views
Search for string in many WBs and post results into another WB
I want to search for a string inserted in TextBox1 on Mainwb within a range of data located in Workbook ...
5
votes
0answers
35 views
Function to conect to Firebase Rest API via Access VBA
This is the application for small school, which will store students' info in Firebase and also retrieve data from Firebase. I am using JSON Parser to parse the data and save the same in Access tables.
...
6
votes
3answers
65 views
Parsing excel cells containing line feed characters
This is a direct follow up to 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 ...
12
votes
1answer
67 views
Shared VB6 *and* VBA extensibility add-in with OnConnect and OnDisconnect handling
Rubberduck works well enough when the VBE is of the VBA variety, but it currently only works when the Rubberduck is configured to load at VBA start-up, and it has a tendency to crash the host ...
6
votes
2answers
56 views
Output the average values of binned columns in Excel VBA
This code is meant to run through a column of values, bin the values based on specified ranges, then output the average value of each bin. The problem is the code is running quite slowly (...
8
votes
1answer
94 views
Pasting Values To/From a Calculation Model
I have a Database worksheet (SoapUI - CollisionsSingle) that have around 1500 records and 38 columns of data per record. I have a VBA script that copies the records ...
5
votes
1answer
59 views
Determining whether a date is within a range
I am taking a user input which consists of a from date and a to date. Those dates are compared against 4 cells in a row - a post date, pause date, resume date, and a close date. My goal with this code ...
4
votes
1answer
41 views
VBA Print Summary Pages - Excel - Runs Slow
Looking to speed up this code some if possible. There is a main page with this macro assigned to a button. Once the button is pressed it ask if you wish to continue and mentions it takes a while to ...
1
vote
1answer
58 views
Copy and paste to different worksheets
This code:
Copies a value from "Sheet1"
Pastes special (as a number) in another sheet "Sheet2"
Jumps to the next line
I ...
12
votes
2answers
451 views
Regex Matching a Naming Convention
Program Purpose
So, I have a naming convention for certain folders.
I want to take in a folder name, and determine if it conforms to the convention.
Naming Convention
The convention (case ...
3
votes
2answers
41 views
Take all underlined words, put in Excel column
I have a 100+ page Word document, that I would like to extract all of the underlined words from. I am very familiar with VBA and Excel, but relatively new to Word. I was wondering if this is the best ...
2
votes
3answers
47 views
Converting a range in excel to a single column with excel vba
I am very new to coding and know only the basics. The first part of this code is running fine. It converts a range of values to a single column. However, with my data set the rows of data step down,...
6
votes
1answer
52 views
Validating FileSystem Structure
I have a File System. It is *supposed* to be laid out / used / added to in certain ways.
This is a program to report on the *actual* state of the file system versus what it's supposed to be.
In ...
1
vote
2answers
219 views
Excel VBA used to fetch Access data
I am using the following VBA to fetch data from an Access database. The query works but there is a lot of information in the database and the response time is very slow, about 30 seconds.
Is there ...
0
votes
1answer
32 views
VBA function that returns color RBG code of a referenced cell [closed]
I have this function I'm building to return the RBG code of a cell I input. The code "works" but something funny is going on. If I write multiple functions each referencing different cells, then all ...
3
votes
1answer
58 views
Partial anagram generator in VBA Excel
I'm trying to make a VBA code to arrange k-element subset of an n-set into some sequence. In other word, I'm trying to list all of k-permutations of n member set. For example, let's try to list all of ...
2
votes
0answers
28 views
VBA - Cycles through a table to find the cheapest bearing that passes
I have written several VBA macros to run on an excel spreadsheet. Inputs are entered into a table and then the macro is run; it cycles through a table of Bearings (in price ascending order) and ...
5
votes
0answers
39 views
Managing a programmatically accessible stack trace
VBA has a call stack... but there's no programmatic way to tap into it, which means in order to get a stack trace for a runtime error, one has to manage it manually.
Here's some example code that ...
5
votes
1answer
53 views
VBA to MS-Access SQL query set(s) and outputs
I am hoping I can get some guidance on some better "best practice" suggestions on how to handle this kind of code set. I understand that parameterized queries are a thing, but I am not quite there yet,...
1
vote
1answer
50 views
Generate SQL for translated resources from Excel Spreadsheet
I do some work that involves translating resources for a webpage. These are all stored in a SQL Server table that looks like this (plus a few irrelevant constraints).
...
4
votes
1answer
61 views
Web scraping VBA and VB Script
I am working on a project on VBA where the objective is to have a "program" that fetches rates from a website called X-Rates, and outputs to excel the monthly averages of a chosen country.
Initially ...
3
votes
1answer
27 views
Split cells vertically on comma MS Access 2010 VBA Module
This takes a field delimited by commas, and splits on the comma. The newly divided information is put in another field (created at the start of the program) called ...
6
votes
2answers
72 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
219 views
Word VBA function to find a specified heading & return its text as a string
In this post, I asked for an overall review of a script to auto-generate table captions, because it was running relatively slowly. I've used Debug.Print to isolate the problem, and this function takes ...
5
votes
4answers
135 views
Trying to make more simple VBA
I have few models like this below for diferent tools i manage. I am trying to clean/improve the code... Could you help me?
All the informations comes inside columns B to N, and the column P ...
4
votes
2answers
54 views
Simplifying manual data entry with a UserForm Excel 2010
I am an absolute beginner. This is the first thing I've ever coded in VBA. I don't know if I'm allowed to ask for just criticism in general without a question.
But just in case I can't, this file is ...
5
votes
1answer
44 views
Word VBA Code to Automate Table Captioning
I have a Word VBA script that auto-generates a table caption based on user input & a previous heading. Its purpose is to speed up editing & formatting of massive software documents (like the ...
5
votes
3answers
57 views
CopyOf2DArray (Values Only)
Purpose:
Given a 2-Dimensional Array, return a copy of the Array. In VBA, if I just
Set newArray = oldArray
then newArray is ...
5
votes
3answers
65 views
5
votes
4answers
206 views
Get Filtered Data Range in a Table
Purpose:
Given a Worksheet Table (ListObject) that may, or may not, have a filter currently applied to it, return a range consisting only of the filtered data.
...
11
votes
1answer
153 views
Finding the cheapest hotel
I thought this question made a good excuse for some basic OOP code, so I whipped up a little bit of code to demonstrate it in VBA; the idea is to have tutorial-grade code, to show how interfaces and ...
4
votes
2answers
55 views
Accelerate the process of filling the quality control table
I'm working on a report of quality control. I've got a table, with all the points of control inside, and each time, when I get a new test result, Excel should fill this table. My code already works, ...
3
votes
3answers
84 views
Searching values of range X in range Y
This allows me to search each cell J4:J6525 in range K4:K6525. If it is found it will paste the values in Offset(0, 2) (column M).
I run it for files with more than 60000 lines, and sometimes much ...
4
votes
2answers
40 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 ...
1
vote
1answer
40 views
Finding and deleting a certain word
I have a working macro that finds a specific word and deletes the whole text. It finds the word "JOBSPECIALTYCODE" in the sheet, selects and deletes it, then moves to the next sheet. I just want to ...
4
votes
1answer
55 views
Copy sheet contents faster in Excel
I have code that opens a workbook and copies the sheet to the workbook running the code. I know activating is generally bad for speed but I can't figure out how to speed this up anymore. The data ...
2
votes
1answer
34 views
Converting values to Dates in column
I'm not happy about my code, but is the better way that I find,
I'm not happy because I see that there are many code repetitions and when any change is easy to make mistakes. Do you think you can do ...
3
votes
1answer
103 views
Comparing two lists
I'm working on a report of quality control. I have a list L, which contains the names of files that I should open and import the data into EXCEL, then fill my quality control table by these data.
And ...
8
votes
2answers
49 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 ...
