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.
1,296 questions
2
votes
0
answers
181
views
Indexing into Nested Data Structures in VBA
Project
I have created a VBA module called Idx which you may find here on GitHub. It is inspired by pluck() and ...
- 569
1
vote
0
answers
114
views
Counting the number of instances of a class module in VB*
In VBForums I found the following post:
Is there a way of retrieving the number of instances of a VB* class from within the class itself?
One could store an instance counter in a Public variable in a ...
- 51
6
votes
1
answer
117
views
Excel Bookkeeping Application, Create Ledgers from Chart of Accounts and Journal
I am writing an Excel bookkeeping application that would be suitable for a small business or contractor. The application is based on a book on double entry bookkeeping:
https://www.amazon.co.uk/gp/...
- 2,531
7
votes
2
answers
335
views
VBA: Efficiently Organise Data with Missing Values to Achieve Minimum Number of Tables
The task is to create sub-tables from an original table (like below) that contain the missing data columns (missing data is highlighted in red). The important part of this task is to make the smallest ...
- 71
3
votes
0
answers
155
views
Evaluating WinAPI, PowerQuery, PowerShell, and FileSystemObject for File Retrieval Using VBA
Traditionally, VBA developers rely on the FileSystemObject to retrieve files within a directory. I decided to test and compare different methods (Windows API, PowerQuery, PowerShell, and ...
- 4,328
2
votes
0
answers
310
views
Simulating Objects with Collections in VBA
Update
I have released a prototype (v0.1.0) of my SOb project, which is a generalized framework for easily simulating objects.
Background
I am writing a VBA module ...
- 569
5
votes
1
answer
167
views
Update Member "In Place" within VBA Collection
Background
I encountered this issue a while back, where I tried to "set" the value of a member "in place", without changing its position within the ...
- 569
-1
votes
1
answer
81
views
Need assistance modifying a "too large" procedure [closed]
I have a chunky VBA script that needs some help making it efficient, which I think will correct the "Procedure too Large" issue I just ran into.
I run the procedure from an open workbook ...
2
votes
1
answer
250
views
Which is faster: Unhide rows during IF or Unhiding them all at once in another macro?
I have process where I am generating workbooks from a list. One of the tabs has varying height and I have a formula that my macro uses to determine whether to hide a row.
As I move to the next ...
- 211
3
votes
1
answer
177
views
userform with textboxes and dates to show timeline
I am writing the following VBA code to make a timeline, which I will be using for other parts of the code later on for other calculations and output. I have a userform where the following number of ...
- 41
3
votes
0
answers
501
views
Iterate internal array for a VBA class
After spending a considerable amount of time poking at the internals of the Collection class, I have discovered 2 methods to iterate a private internal array within ...
- 1,776
2
votes
1
answer
175
views
One way Synchronize 2 Access Tables with VBA ADODB
I am synchronizing two MS-Access databases.
One of them on a network drive, the other one on the local machine.
(For testing purposes both in the same directory)
The Databases both contain a table ...
- 21
4
votes
5
answers
991
views
Efficiently creating an array from a filtered sheet
[edit] I realised why everyone does this with loops; my plan was to use .SpecialCells(xlCellTypeVisible).EntireRow.Address to return the row(s) of the results of the results of the filter, but that ...
- 103
6
votes
1
answer
797
views
Transcoding UTF-8 to UTF-16-LE in VBA
VBA is a language that's lacking a lot of basic functionality. (Pun intended)
Most libraries, if they exist in the first place, are OS-specific, and even some of the inbuilt functions don't work on ...
- 195
0
votes
1
answer
125
views
VBA Excel - autosum values from all workbooks in the directory
I have the code such as this for opposite conditions.
...
- 259
3
votes
1
answer
661
views
Excel VBA to powerpoint
I am basically looking for a way to trim below code. It works just fine.
This code takes a range from each tab of an excel spreadsheet and pastes it into a powerpoint file, then assigns a title to ...
- 31
1
vote
1
answer
569
views
Optimising Read/Write Speed of Excel VBA Copy/Paste Macro
I have an Excel sheet that connects to third party software which populates Sheet1 with data. It does this multiple times per second and overwrites previous data.
I have written the macro below to ...
- 113
3
votes
3
answers
767
views
VBA Very Slow Performance using objects with scripting dictionary
I have a simple code that loops through a text file to fix a few things in it and I use mostly dictionaries to find and handle the adjustment. However, with larger files (about 30K lines) it seems to ...
- 31
4
votes
0
answers
255
views
UDF to Generate Hyperlinks in Excel
Background
I am trying to recreate the functionality of HYPERLINK() on my own terms, as a custom UDF called Hyper_Link(). My ...
- 569
2
votes
1
answer
135
views
Inserting Rows after Contiguous Set of Integers - Excel VBA
I have a repetitive data reduction where I have a list of ID's that I want to find in a longer list. I have a method, but it is slower than I'd like. How can can I speed this up?
The process: After ...
3
votes
2
answers
290
views
Sub that calls itself when its parent Workbook is closed
and no, I'm not talking about Workbook_BeforeClose. Note that the motivation of this question is Excel specific but the technique I'd like feedback on is not.
If ...
- 195
2
votes
1
answer
245
views
Create a string "Build" number for procedures and module
The class assigns various strings to the build number. So the build might look like 3.4.5.1 which would be of the format ...
- 121
2
votes
1
answer
830
views
Hiding layers in Visio with VBA
I created (basically) an organizational chart in Visio with different levels and want to be able to hide the levels with the press of a button. If I hide level 1 (= top level), all levels below it ...
- 161
0
votes
2
answers
107
views
VBA code which exports data from Access to Excel and then loops through the Excel file
I have a couple of VBA loops that work in the blink of eye when I execute them through Excel, but doing this as part of an Access VBA application takes like 15 minutes. The loops run through each row ...
1
vote
2
answers
111
views
Copying formulae between sheets
I am currently running multiple macros on a large volume of files however watching it go through its processes today I realise one of my issue is that my loop runs through my workbooks 3 times instead ...
- 21