All Questions
56
questions
15
votes
2
answers
2k
views
Passing a 1D array (single column of a Range object) to .NET to manipulate it without loops and returning it back to VBA
Background
Kind of a follow up - slightly related to my other SO question.
I thought that if somehow I find a way in VBA to pass a single column to .NET and convert it to a native .NET type then I ...
11
votes
1
answer
283
views
Find all differences between 2 strings
I pulled this out of my code bucket and dusted it off earlier today in response to a post over on SO that made me cringe. This was originally written to highlight changes in Excel cells in real time ...
10
votes
2
answers
387
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
votes
2
answers
209
views
Report Building (Data Retrieval, Validation, Aggregation, Business Logic, Report Building, Visual Presentation)
This:
Is a data table we get from our financial platform with lots of useful information. For reference, "--" is also the string they use to denote empty values.
This:
Is a spreadsheet I built (...
6
votes
2
answers
9k
views
Copying from sheet to sheet if column headings match
Basically, I have three sheets in my workbook where only the second and third worksheets are being used. I want to copy columns from the second sheet to the third sheet only if the column headings ...
6
votes
2
answers
319
views
Deca dimensional ascending and descending array sort function
Some folks told that a two-dimensional array can solve almost all the problems, but let me disagree with that. That's why I made this Deca dimensional array sort function. The array must be ...
6
votes
2
answers
495
views
Return index of array where sum first passes n
I have an array of integers and a number n. I want to sum the elements of the array and return the index where the sum passes n,...
6
votes
1
answer
448
views
Fastest function to `Remove Duplicate Lines` per each cell
The below function is used to Remove Duplicate Lines per each cell.
It works without problem, but it is slow with a range of only one column and 17k rows.
Actually, ...
6
votes
2
answers
112
views
Find select values on one worksheet and copy to a second
In the Calculator worksheet of my spreadsheet, for any appliance type present in a building being studied, users enter a quantity. There are 18 different appliance ...
6
votes
1
answer
147
views
Get Worksheet Data Array (Standard Methods)
I'm re-writing my module of Standard Methods. Virtually every project I do begins with grabbing some number of Data Tables and putting them in arrays. So, this is my general "Get Worksheet Data" ...
6
votes
2
answers
1k
views
Ranking a variant array with variable dimensions
I'm doing VBA macros and need to use Arrays of values extensively. After the below help from @TonyDallimore on Stack Overflow, I've decided to use nested variant arrays.
VBA chrashes when trying to ...
6
votes
1
answer
388
views
Excel's Dynamic Array Functions...In VBA (No Win 32)
In recent months I have been trying to figure out how in the world one can mimic the functionality of Excel's New Dynamic Arrays exclusively in ...
5
votes
4
answers
189
views
Check non-continuous range for equal values
Background
I'm trying to check equality between cells in an irregular, non-continuous range of cells. For this purpose I would like to know if all cells are equal to the first ...
5
votes
3
answers
97
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
2
answers
1k
views
Text files: Copy, Rename, Append/Merge together
I wrote 3 subroutines related to batch data processing, they will be used together. A bit of background, I wrote this for my admin colleagues who do not write code. An application dumps daily ...