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

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.

2
votes
1answer
28 views

Date input wrapper for TextBox

Purpose I managed to get completely nerd-sniped earlier today by a question over on SO and decided to take a stab at adapting my numeric input wrapper for TextBox...
0
votes
0answers
29 views

Allocating caller information to a specific source

My current code takes a set of caller information (calldataFile or CD_) and allocates it back to the specific source that it ...
0
votes
0answers
18 views

Loop each items are the unique solution to retreive one specific property from one collection object?

with this code I add item/properties into my collection: ...
3
votes
0answers
41 views

So what if the “View” in an MVC isn't a Userform, but is the Worksheet?

So I've been following the discussions around VBA as an OOP language and the applicability of various design patterns, such as MVP or MVC. Excellent postings on these topics are extremely informative ...
3
votes
0answers
25 views

Responding to Events of Dynamically added Controls

I was asked for an example of a class to wrap dynamically added controls as I suggested in this Answer. Although it is out of the context of a Code Review, I thought that it would make an interesting ...
2
votes
3answers
71 views

A function that replicates DLookup, and can use parameterized queries

This function was built to replace DLookup so that i could pass in parameterized queries for a speed increase. I also wanted a simple way to get more than one value if needed. For example, if i want 4 ...
1
vote
1answer
41 views

Loop through different controls and enabled the state of the whole group VBA

I got the below code that is looping through all controls and changing the state in a after update event and enables new row for user to use. Everything works fine but i'm just wondering whether there ...
5
votes
0answers
97 views

Web Battleship -The Unofficial Battleship UI

I've written my own UI for Mathieu Guindon's VBA Battleship (Battleship UI: GameSheet) which uses a webpage in a WebBrowser control for the View. Webform: Userform ...
4
votes
2answers
108 views

Space Invader Style Game - Model / Presenter Decoupled From View - Functional Again Too

Thank you for your patience. The game is fully functional again! I have tried to incorporate all the advice I have received so far. Thank you for taking the time to review and comment. I have now ...
1
vote
1answer
50 views

Space Invader game written In VBA, 3rd iteration [closed]

Here is a link to the workbook, classes, modules and forms: https://github.com/Evanml2030/SpaceInvader/tree/master Positive: I have decoupled the view from the control / presenter. I have ...
2
votes
0answers
38 views

Unit testing a custom collection

To enable unit testing in the VBA IDE I'm using https://github.com/rubberduck-vba/Rubberduck. I've created a class and a custom collection class to work with structural members I need to filter. The ...
1
vote
1answer
63 views

User form that searches a spreadsheet

I have this userform that looks up into a sheet. The problem is that when there are a lot of rows to look up it takes too long to execute (for example 100 thousand rows). Is it possible to reduce the ...
2
votes
2answers
90 views

Index Match implementation

The code below takes header variables, retrieves the column index, and then, using an Index/Match function, returns data from a matching account number. The reason it is written this way is because I ...
1
vote
1answer
37 views

Sending data from sheet to other sheet in Excel (from a dispatch note to some records)

I have this code to save data into some records. It is a little slow, so how can I make it faster? To the first record (sheet MOV MERCADERIA) it copies 23 columns ...
3
votes
2answers
118 views

SpaceInvaders Second Iteration - Fully Functional - Next Up New Presenter Class

Going to take a look at Rasta's comment below tonight and just realized I am using byref to pass everything again - DUHOH - Old habits die hard I have revamped with some of Comintern's improvements ...
2
votes
1answer
105 views

A function that tells you if a particular set of data exists in a listbox

I created a function that checks to see if a particular set of data has been added to a listbox and returns a boolean to indicate if the value was found or not. I am asking for critiques on my code. ...
2
votes
2answers
95 views

Automating the creation of a weekly schedule

I'm trying to build an Excel workbook to automate and aid in the creation of a weekly work schedule. My current workbook is functional, but it's slow - especially on one task where I have a list of ...
7
votes
1answer
210 views

Space Invader Style Game Written In VBA

EDIT: Link to the github, https://github.com/Evanml2030/SpaceInvader/tree/master EDIT: SpaceInvaders Second Iteration - Fully Functional - Next Up New Presenter Class ^ a link to second iteration. ...
3
votes
0answers
121 views

Await in… VBA?

Asynchronous programming is tricky enough already, and VBA doesn't make life any easier. I wanted to create some basic functionality to allow async processes to mark completion without having to ...
8
votes
2answers
147 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 ...
5
votes
0answers
141 views

Battleship MVC Architecture

The entry point is the Macros module, which - for now - includes only a single procedure, at a very high abstraction level - I'm quite happy with this: ...
1
vote
1answer
40 views

Copying data from various sources into a master list

I am working on a Master List, where I am copying data from various sources for each month into the columns Z, AC, AF, AI etc. (always separated by 2 columns). Then I copy that cell all the way down ...
1
vote
0answers
37 views

Insert Emojis Into Outlook Email With VBA Application (For those of us who cant use add-ins)

I cannot use add ins here so I set out to create an excel application that could get the job done. I suppose that I could easily transport this into outlook? Basically I want to click one of the ...
1
vote
1answer
34 views

Function to get constants of range

The following small function receives a range and returns it with only the cells that contain constants or Nothing if there's none. ...
1
vote
1answer
81 views

VBA Script to Remove Duplicates

I'm curious if there's any way I can substantially improve its speed (it runs through >200k rows). I think I'll most likely need this once, so please bear with me since it probably looks terrible. ...
0
votes
1answer
39 views

Copying ranges from multiple Excel sheets into a main sheet

I have a VBA code which copies same data from Multiple sheet and then paste it in "Main" Sheet. It then auto fills the blank cells for values from above and then it delete all the rows Where H:H is ...
7
votes
1answer
129 views

Solution for Parent/Child circular references: WeakReference class

Sometimes in advanced OOP scenarios, a class needs to hold instances of another class which needs to hold a reference to the "parent". For example when you have a dynamic ...
3
votes
1answer
94 views

Running SQL Queries On A Worksheet (With Set Of Random Numbers Between 1-20 Filling Column A) Handled As An ADO

INTRO I am using an Excel worksheet as my "database." No headers so [F1] is assigned to column one by default. I filled the entire column, all 1,048,576 cells with ...
2
votes
1answer
28 views

Compensation Year-End Planning VBA Script

I have a pre-planning file for year-end review that does a lot of index/match formulas and IF statement formulas based on certain parameters when referencing ...
7
votes
3answers
60 views

Evaluating Dice Poker results

In Dice Poker (or Yacht), a player rolls 5 standard dice (sides 1 - 6) and the resulting roll is given a score based on combinations: Two matching dice only - pair Three matching dice only - three ...
8
votes
0answers
100 views

BattleShip - Ships & Players

I'm making a full-blown OOP Battleship game in VBA (previous post: Battleship Grid), and I want this code to be as good as it gets - and better, if I can. I've refactored a few things, introduced a <...
10
votes
1answer
115 views

BattleShip Grid: Classes and Tests

In my ongoing quest to demonstrate how VBA code can absolutely be object-oriented, I've started implementing a game of Battleship in pure VBA. This is a rather large project, so I'll split the ...
1
vote
2answers
70 views

VBA - faster way to list out files in a folder & subfolders

I have written a VBA code in excel (basically through combining various codes from others -_-") to list out the path, file name, file size & extension of all files under a folder and the sub-...
2
votes
2answers
52 views

Conditional highlighting (Font size colour, size and Bold) using VBA

Note: Question originally asked in SO. Objective of Code In my worksheet I have several columns that need to be compared to other columns. None of these column pairs are contiguous, so it can be ...
0
votes
1answer
60 views

Class Module of Table properties, Record Table HeaderName and Column number to dictionary

This code creates a class module that record headers in to a dictionary which then allows for copy and paste data from another file where by the headers match. This helps to create a more dynamic ...
3
votes
1answer
68 views

Housing a summary of information in a data file

So far it is working great, but I was wondering if I could optimize a little since I have nearly 30 variables in code of only 100 lines. Since I'm not a coder, I code based on how I would be doing ...
6
votes
1answer
146 views

Sudoku-lite challenge

You can find the problem here. The example: Column A, B, and C are each 3 and together equal 9: ...
2
votes
1answer
60 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 ...
4
votes
2answers
68 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
91 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
103 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
38 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
64 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
308 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
43 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
79 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 (...
1
vote
2answers
137 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
131 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
57 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 ...