Questions tagged [excel]
Microsoft Excel is a commercial spreadsheet application written and distributed by Microsoft.
153
questions
14
votes
1
answer
2k
views
VBA Class to persist and restore Excel Application properties
Very often on Stack Overflow, and even on Code Review, I've seen questions and answers that have code that begins by persisting Excel properties like DisplayAlerts ...
- 1,969
7
votes
1
answer
335
views
Comb Sort in VBA
Playing off Robust Bubble Sort in VBA and as suggested by @Henrik, I took a look at comb sort and tried to create an algorithm based on the documentation solely on Wikipedia.
Basically, the first ...
- 7,149
1
vote
2
answers
530
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, ...
- 7,149
14
votes
1
answer
1k
views
Tick. Tick. *breathe* BOOM! - Setting up real, stable asynchronous callbacks with WinAPI Timers in VBA
WinAPI Timers can be quite tricky to work with, as anyone who's tried to use them and fallen foul of one of the many pitfalls probably knows. Problems such as screen freezing, crashes, uncontrolled ...
- 2,335
6
votes
1
answer
432
views
Non-standardised data across multiple worksheets: Aggregation, Validation, Filtering
Every piece of business that gets written (E.G. an adviser's client puts an additional £10k payment into a pension plan) goes in a spreadsheet called the SubSheet. It's split into 8 worksheets for ...
- 8,800
6
votes
1
answer
531
views
Looking up ingredients and creating a shopping list
This is my first attempt at refactoring - FindIngredients and I'm wondering where I can improve. The github link in the code directs to the project that still has ...
- 7,149
5
votes
2
answers
132
views
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 is the way it exports to excel -
With the problem being that these cells are ...
- 7,149
4
votes
3
answers
346
views
Brute force looping & formatting Or Create Union range & Format? Which is efficient and when?
May be my question is not up to the standard for Code Review, but of upmost importance for reassessing methods used in my VBA coding . While preparing/testing answer for a SO post (thought to simplest ...
- 278
3
votes
3
answers
113
views
Follow Up: Refactored C# Tool to Generate MS Word Document Mailbox List From MS Excel
Background
Some very good observations were made about my original code in the 2 answers to this question. In this version I have attempted to reduce cyclic complexity and class coupling even more as ...
- 22.4k
3
votes
2
answers
112
views
C# Tool to Generate MS Word Document Mailbox List From MS Excel
There is now a Follow up question to this question.
Abstract
The VBA solution didn't work on the clients computer because Office 2010 Starter Edition doesn't support VBA. I decided to try a C# ...
- 22.4k
2
votes
1
answer
722
views
Part 2: Send HTTP request for each row in Excel table
This script was previously reviewed here: Send HTTP request for each row in Excel table (Part 1)
I've made the changes that were suggested in the Code Review answers as well as added a few of my own.
...
- 198
20
votes
1
answer
5k
views
Importing data from an external EXCEL-Sheet
I was assigned the task of copying some subsums from a given EXCEL-Sheet into the executing EXCEL-Sheet.
This had to be done with an EXCEL-Macro, so non-programmers can easily use it. There was the ...
- 25.2k
12
votes
3
answers
787
views
Perf wrapper for Excel VBA
Spoiler
This code is actually BAD for performances! So don't use it! ;)
For a while now, I've been using this wrapper to avoid retyping most of it :
...
- 359
8
votes
3
answers
333
views
If ComparisonIsTrue(thisPost, ComparisonOperator.NotEqualTo, goodCode) Then Me.Answer
This is a follow-on to a global enum for comparison operators. I wanted to pass a logical expression E.G. >= someValue as a parameter to a function, so I made an ...
- 8,800
8
votes
1
answer
183
views
A global Enum for comparison operators
This approach was born out of wanting to pass a logical expression (E.G. ">=10000") as a parameter to a function.
So, I built an Enum and a function to use it to evaluate logical expressions.
Is ...
- 8,800
8
votes
0
answers
1k
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. ...
- 4,068
8
votes
2
answers
395
views
8
votes
0
answers
3k
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 ...
- 4,068
7
votes
2
answers
9k
views
Traversing and printing complex Dictionary types (Scripting.Dictionary) - Dictionary inside of a Dictionary
Based on this SO post - also reposted on vba4all.com with a few more details and explanations.
Please notice there currently is no error handling whatsoever as I didn't analyse and consider any traps ...
user28366
6
votes
2
answers
239
views
Copy a contiguous sub-column of cells
This code searches for the word "Disbursements" and copies all of the rows with data below it, including the row with the specified word. After the rows are copied to a new sheet, the ...
5
votes
1
answer
522
views
Managing Excel Tables (ListObjects) with OOP Approach
Objective:
Manage what happens when users interact with Excel Tables (ListObjects)
Possible interactions:
Update an existing Excel table
Add rows/columns to the table
Update a cell or a range of ...
- 425
4
votes
0
answers
109
views
Comb Sort Multi-dimensional Array on Key
Previously: Comb Sort in VBA
I've been running two different comb sorts (on arrays) depending on whether I want to sort numbers or strings and I figured I would give using ...
- 7,149
4
votes
0
answers
134
views
Print apartment mailbox lists from excel
Update
Due to the fact that the client's version of excel does not support VBA, I have moved on to a C# excel interop solution which has been reviewed.
The test data has been added at the bottom. The ...
- 22.4k
4
votes
2
answers
145
views
User interface for a simple tool that generates Word documents from Excel data
This question provides the code behind the dialogs in the C# refactoring question. I'd like a review of this code as well. I'm not currently using databinding anywhere, and there is really only one ...
- 22.4k
4
votes
0
answers
439
views
Looking for more efficient VBA to Move data from Teradata to Excel
I have a simple macro moving data from Teradata to Excel. The process normally takes me 10 minutes to perform manually, however the macro can run for hours sometimes. This is making the macro seems ...
- 51
3
votes
1
answer
666
views
Managing Excel Tables (ListObjects) with OOP Approach (Follow up)
This is a follow up to this question
Objective:
Manage what happens when users interact with Excel Tables (ListObjects)
Code incorporates:
Greedo's answer suggestions:
Listen to an encapsulated <...
- 425
3
votes
2
answers
361
views
Retrieve, total and display ingredients of meal
Here is the first iteration of this project on CodeReview
Note: The input meal is data verified - it cannot be something that doesn't exist on the lookup sheet. ...
- 7,149
3
votes
1
answer
221
views
VBA syntax that compiles sub data into master sheet
I am using this syntax to bring in the names of all employees whom are active, then from there filter the data on subsequent worksheets and use a formula to bring the data over to the ...
- 247
3
votes
3
answers
6k
views
Change filename of Excel-files based on the content of a cell
I have hundreds of Excel-sheets looking something like this:
I can't use the actual sheet, and not the exact document numbers, since they are confidential.
The name of the files are something like
...
- 2,057
2
votes
1
answer
73
views
Making a report from payroll details part II
Thanks for the help so far! Especially all of the people who helped out on part I - seen here. Making a report from payroll details
Here's the revisions to the code based on feedback from that thread....
- 277
1
vote
1
answer
191
views
Looping through with varied subtotal ranges
I've got an issue where my looping through the rows on my sheet are extremely slow. For 20,000 rows, it takes about 20 minutes, whereas before, it was taking 3. The only pieces I added were at the ...
- 143
1
vote
1
answer
97
views
Changing the formatting of sheets
This my program to change the formatting of sheets in different folders. I have over 5000 files in one folder and it is taking up to three hrs for one folder and there are multiple subfolders in the ...
- 111
1
vote
0
answers
111
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 ...
- 415
22
votes
1
answer
27k
views
Snake in Excel in... VBA?
snake-game is a game where you control a snake in an enclosed square without running into the walls or your own body. The snake grows when you run into whatever item you are supposed to run into.
My ...
- 7,149
19
votes
2
answers
390
views
Randomizing Civilization 5 team choice
This macro is made to randomly draw specific number of civilization choices for a specific number of Civ V players. It is based on a list of 43 possibilities and the ...
- 311
14
votes
1
answer
120k
views
Reading data from Excel sheet with ExcelDataReader
Objective:
I want to import an Excel file, and read the rows of certain columns. For this, I use ExcelDataReader. I've implemented a low-level class called ...
- 143
12
votes
3
answers
6k
views
Parameterised Constructors
Finally, Parameterised Constructors are possible in VBA.
Background:
One rainy day, I came up with an idea: take a static class and expose one property as default (like it was done in the Reverse for ...
user28366
11
votes
2
answers
13k
views
Searching a Word Document from Excel
Today I created a script in Excel that migrates data from a specific Word document and copies a portion of it to a cell in Excel (the date to be specific).
The file input is
Last name, First Name, ...
- 357
11
votes
1
answer
638
views
Copy, Paste And Format
I am currently working with this code to automate some tasks for senior staff members that are not very adept in Excel. Wondering if VBA is simply not a very quick code or if my code is clunky and ...
- 177
11
votes
1
answer
149
views
Function to find the kth match
I've created a UDF some time ago and used it pretty often till now in different ways (but mainly to compare a "history" of data like having different filters at the same time). While there is no need ...
- 683
11
votes
4
answers
663
views
Get Workbook Method(s)
I'm re-writing my Module of standard methods. These 3 are used to retrieve Workbooks as Workbook Objects. The standard I'm aiming for here is "Third-party Library/Add-in". So:
Do these functions ...
- 8,800
11
votes
1
answer
716
views
Standard Methods in VBA [closed]
Since I learnt about refactoring, I've been busy creating my own Module of Standard Methods. I feel it's about time they got a review of their own.
Open to all aspects of advice but particularly ...
- 8,800
10
votes
2
answers
379
views
Creating a pseudo Pivot Table / Database using a 4-D array
Why am I not just using a Pivot Table / Database?
a) I've never ever used either before. And I don't have time to learn how before this project needs to actually be finished.
b) The final output ...
- 8,800
10
votes
1
answer
523
views
Produce a Linear Cut List for Woodworking
I was inspired to write this by a recent review of cutting pipes.
The only thing I'm really iffy about is using the WriteCuts method. I couldn't figure out a way to ...
- 7,149
10
votes
3
answers
1k
views
Macro that does some kind of duplicate check
This macro pulls in two workbooks, one being a template with saved formulas already, and the other containing data with thousands of rows...I need to increase the speed because the process takes more ...
- 125
10
votes
2
answers
416
views
Financial Data From Webqueries in Excel
I'm new (to CR and to programming in general). I wrote my first VBA on Monday. This is my first working project. It Takes a bunch of financial data from a company called Financial Analytics and a ...
- 173
10
votes
2
answers
832
views
Conditionally deleting rows in a worksheet
I have used loops to remove lines that are doing various things, but all are deleting rows where certain criteria is met. Some are deleting less than certain numbers, deleting blanks, removing ...
- 101
9
votes
2
answers
399
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 ...
- 74.9k
9
votes
2
answers
2k
views
Text file to spreadsheet
Lets say you have several .txt files in a folder and want to merge all text files into a single Excel document:
A.txt
...
- 3,026
9
votes
3
answers
215
views
Check Accounting Journal for Errors
I wrote this sub in Excel VBA. I am a novice, but I was able to get my code to work how I wanted it to.
Unfortunately, I can see there is a lot of repetition, but it is too complex for me to ...
- 213