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
1
vote
0answers
31 views

Resolving circular references in VBA via ObjectStore class and CleanUp cascade

Resolving circular references in VBA via ObjectStore class and CleanUp cascade I have been working on a VBA database library and realized I had a few circular reference loops in my class hierarchy, as ...
1
vote
1answer
61 views

Predeclared class instance tracks the number of regular instances

Predeclared class instance tracks the number of regular instances In VBA, predeclared class instances can act as object factories, and in general, it makes sense to keep this instance stateless. ...
3
votes
1answer
66 views

Checking if date falls within a date range then write the date range to a cell

I want to simplify some code I have written but not sure how to go about doing it. When my workbook opens it checks the current date and compares it to dates I have on a sheet. If the current date is &...
0
votes
1answer
30 views

Macro which performs a series of operations on a given set of data

This macro calculates some basic figures from a given set of data. But it runs very slow probably due to me referencing the sheet again and again. Does anyone have a better idea on how to optimize ...
2
votes
0answers
71 views

Evaluate performance of DLL calls from VBA

While playing with a VBA project involving Windows APIs, I have noticed some odd behavior: API calls under Excel 2016/VBA7/x64 appeared to be much slower than under Excel 2002/VBA6/x32. Since I ...
4
votes
2answers
1k views

VLOOKUP Macro to reformat data between sheets preserving the connection to the source

Hello I'm relatively new to VBA and I have this macro to VLookup information into corresponding cells in the sheet that works, the issue is that it depends on a For Loop to iterate through 10s of ...
1
vote
2answers
61 views

Copy the last 125 rows from a selected excel file and paste into the used one

My task was to get the last 125 rows from an excel workbook. The rows are started from the 17th row and it goes until it can. Here's my code: ...
2
votes
0answers
59 views

My first vba-selenium code

I have been obtaining financial information on an annuity I have which is invested in multiple funds which are proprietary to the insurance company. The asset value is not available other than on ...
2
votes
2answers
66 views

Comparing Multiple Columns to Remove Duplicates

This code compares data from two spreadsheets generated from our clinic's software. The goal is to eliminate all the rows from the PaymentsSheet (our most recent ...
3
votes
0answers
122 views

VBA class managing loading DLL libraries

I am playing with a custom-built SQLite library intending to access it from VBA via the C-language API. I keep the library within the project folder, so I load it via the LoadLibrary API. To make the ...
3
votes
1answer
98 views

Collecting all instances of an Office application (Excel, PowerPoint, Word)

A project of mine required me to search for certain workbooks in open instances of Excel. I found this article Code to get Excel, Word, PowerPoint from window handle which returns the applications ...
1
vote
0answers
31 views

Outlook VBA to Replace Tokens from an email template

I'm creating a few macros that open specific Outlook templates an allow you to replace specific tokens before sending the email. It seems to work, but I'm admittedly doing full loops when I probably ...
1
vote
1answer
91 views

Fancy new LAMBDA functions with variable number of arguments to mimic native methods - no VBA

The TEXTJOIN function has a really nice interface: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], …, [text252]) ... where ...
3
votes
2answers
121 views

Sorting worksheet by a given column

My code runs exactly as it should. However, I would like to make it run a bit faster. I have tried defining some variables as Long to run it faster but it is still ...
0
votes
0answers
36 views

Creating Pivot Table taking time at average vales

I have created a VBA macro to create a pivot table automatically. But taking 15 to 20 minutes of time to complete the macro. Especially at the point of below code taking half of the time. Review Code ...
2
votes
2answers
113 views

Forms to enter trading information

My project has grown for a year and now comprises of 19 standard modules, 7 class modules 11 forms. Some of the forms appear to freeze when called but, in fact, they are just slow to respond, taking a ...
0
votes
1answer
44 views

DbRecordset: Updatable recordset with transaction and affected rows count check

DbRecordset is a new class added to a fork of the SecureADODB library, which wraps the ADODB.Recordset class. It complements the functionality of the DbParameters class from the previous post and ...
-3
votes
1answer
61 views

How to reduce the number of loops? [closed]

The following code works perfectly. It just works very slowly. Is there any way to speed up/optimize this code? I think its the amount of loops but I am unable to figure out how to reduce the number ...
3
votes
1answer
96 views

A fluent unit testing framework in VBA

I wanted to get some feedback on a fluent unit testing framework I’ve written. I call the project Fluent VBA. You can find a link to the project on GitHub here Motivation This project was inspired ...
2
votes
0answers
54 views

Extending the SecureADODB library

SecureADODB is a very instructive VBA OOP demo. I have played with SecureADODB for a while and plan to integrate it into the prototype of my database manager application. In my previous post, I showed ...
0
votes
0answers
23 views

Batch Insert Multiple Figures into Powerpoint Slides with VBA

This is a follow-up question for Batch Insert Figures Into Powerpoint Slides with VBA. I appreciate PeterT's answer. Besides the single image replacement, I am attempting to make ...
1
vote
1answer
124 views

Simple encryption function for Excel sheets

I'm using this function to read a certain set of data in a hidden Sheet1 to do encryption based on the selection by the user. It's all working fine, but its kinda ...
2
votes
0answers
79 views

How to speed up the VBA scraping process

I want to scrape 25000 addresses using Column A cell values as search strings. I'm using the following code, it is working fine but it's too slow to extract 25000 results. Is there any way to speed up ...
1
vote
0answers
73 views

VBA OOP design patterns: factory, constructor, returned interface

While working on my demo database manager app, I have coded a previously presented Storage Library. The structure of the library's code and employed design patterns partially follow that of the Secure ...
3
votes
2answers
108 views

Comparing Three Columns and Copy Pasting data

I am using below code which is comparing three columns values and copy pasting the 4th column data into other column. My code is working fine but it is slow to perform the processing and takes much ...
1
vote
1answer
51 views

ContactEditor - Excel VBA db app: Storage Library and MVP

In the previous post, I presented my Storage Library. Here, I would like to focus on the integration of the library with the MVP component. Functional class mapping First of all, the figure shows a ...
1
vote
0answers
61 views

ContactEditor - Excel VBA db app: Storage Library

ContactEditor uses Excel VBA as a prototyping platform for a personal information manager combining the "Model, View, Presenter" (MVP) pattern and a persistent storage manager. Fig. 1. Data ...
3
votes
1answer
89 views

VBA introspection library for SQLite

SQLiteDB VBA Library is a set of VBA helper functions for the SQLite engine. The primary motivation behind this project is to provide convenient access to extended introspection features. Because of ...
0
votes
0answers
46 views

Fastest Way To Compare 2 Excel Worksheets And Output Differences To 3rd Worksheet

I have a workbook that loads in the results of an SQL query into two worksheets called Old and New. The New worksheet is then edited by an end user. Rows can be inserted, deleted and values changed. ...
4
votes
3answers
199 views

A method for managing enumerations in VBA

I have for some time been frustrated by the limitations around enumerations in VBA. Googling didn't find anything really simple and comprehensive. So after a bit of head scratching I came up with ...
0
votes
0answers
43 views

GUIComparer with MVVM dialog to allow user input in ArrayList sorting algorithm

download Bit of an esoteric title, but let me explain. I wanted to create an IComparer that can be supplied to a sorting algorithm, which rather than using the ...
2
votes
4answers
187 views

To what degree is my VBA Array size function safe, robust, and performant

Coming from a Java/Scala background with a strong focus on OOP+FP, I recently started working with Excel+VBA to implement a front-end for a SaaS. It was like stepping way back in time, over two ...
2
votes
1answer
90 views

Daily Fantasy (DFS) Lineup Generator in Excel VBA

I have a macro I have been using to generate daily fantasy (dfs) lineups for soccer matches. I would like to know where I can speed up my code so I can run more simulations. Briefly, I have a ...
2
votes
3answers
91 views

VBA Vlookup to work faster

I have a code in vba that works well but it is slow so I am looking for a better solution to this code. If there is any one who could help me I would be grateful! ...
4
votes
3answers
193 views

Excel VBA Nested Loop Alternative

I am not an advanced user of Excel and am VERY new to VBA. My only programming experience is 2 C# classes in college. That being said, go easy on me ;) I am working on a team that audits military ...
3
votes
1answer
153 views

Rigorously checking whether 2 floating point numbers are close in VBA

latest updated version in cross-post from SO I'm testing performance regression of some code I wrote (this is not that code) by timing its execution in Unit tests. I would like to see if execution ...
6
votes
2answers
144 views

VBA - Excel add in Convert number to Text currency

No problem yet with the code. Here is a function that can be saved as add in and used as UDF in Microsoft excel to convert a number to text. For example, 4556.45 to ...
4
votes
1answer
124 views

Using OOP/Class Module called "FastMode" to toggle Application object properties in Excel

I'm programming a tool in Excel which reads two exported .csv files, performs some calculations based on Dates and Strings and ...
0
votes
2answers
74 views

Improving Speed of VBA Code - Copy/paste two sets of variable, Excel does a calculation, returns a value then copy/paste the output value

I am attaching the code here. I am very new to VBA and trying to do a complex calculation using Macro. Please help me improve the speed of the attached code. The code works fine and produces the end ...
5
votes
1answer
169 views

Simple python COM server to expose scipy integration to VBA

I want to create a COM server to expose scipy to VBA code, as performing integration is kinda difficult and I don't know of any good VBA/COM libraries for this. My objectives are: As portable as ...
1
vote
0answers
81 views

Replace2 using start and end rather than length

If you already know the start and ending character positions of text you want to replace, this bypasses the need to calculate the length. It just helps eliminate a step by not requiring you to ...
5
votes
1answer
115 views

Copy Columns Forward Macro: From Procedural to OOP

Background: I had created a macro few years ago and when I was reviewing this now, it was hard to understand. I recently went through all the articles from ...
2
votes
1answer
86 views

Best way to copy data to a new sheet and reorganize it (VBA)

I'm writing a VBA program which copies and organizes data from one master sheet into numerous other sheets. One of the recipient sheets unifies all the data from the master sheet which holds the same ...
2
votes
0answers
66 views

Check for circular dependencies

Introduction Sometimes, especially in more complex class structures, I feel uncertain in some way whether undiscovered circular dependencies might exist. So I thought about how I can easily have their ...
3
votes
1answer
71 views

Aggregating hourly location data into daily sublocation data for many columns

I start with an hourly table that has around 40 of items (example: bread, barley, bagels, beef, chicken). The purpose of my code is to aggregate this hourly table's numbers to daily numbers but broken ...
4
votes
3answers
130 views

Excel VBA code midStr() function using left and right position instead of length used with mid()

In the early days of Basic (1970's when i started coding) the midStr() function used left and right positions directly rather than left and length we now use with mid(). I always found it more ...
1
vote
3answers
121 views

Is there some way to simplify this loop

I have a cycle which goes from -55 to -1, then from 1 to 55 before going back to -55 again. I want to make a loop going from an arbitrary point in this loop to another arbitrary point, but never as ...
3
votes
1answer
73 views

Batch Insert Figures Into Powerpoint Slides with VBA

I have a series of images ("1.bmp", "2.bmp", "3.bmp",... ,"30.bmp" in "F:\Images" folder) and I am trying to insert these images one by one into ...
4
votes
2answers
277 views

2048 Game Implementation with Excel VBA

I am trying to implement the game 2048 in Excel VBA. The each TRUE / FALSE Boolean value in row 2, 4, 6 and 8 are used for determining the data of each cell in row 1, 3, 5 and 7 is 0 or not. The ...
4
votes
0answers
95 views

Export/Import Rubberduck VBA Virtual Folder Structure with Library References to/from Project Folder on Hard Drive

Rubberduck VBA permits creation of virtual project hierarchy within the IDE, but the project is imported/exported as a single folder with all files. Sometimes, direct access to the project folder is ...

1
2 3 4 5
26