All Questions
Tagged with dynamic-arrays vba
59 questions
0
votes
0
answers
26
views
How to use complex formulas in data validation via VBA without Excel adding the @ symbol? [duplicate]
I'm facing two related problems while working with VBA in Excel. Both involve complex formulas, but they behave differently when set through VBA compared to when entered manually in the Excel UI.
...
2
votes
1
answer
155
views
Does redim use the heap or the stack memory in VBA?
I know this is quite strange to ask for a language that is far from a low-level language such as C and C++, but it caught my attention that for example if I do this:
Dim tempArray(0 To 2) As Integer
...
- 63
0
votes
1
answer
115
views
VBA function does not return dynamic array
I am using Excel365. Below function is intended to have SumProduct work with arrays that contain empty cells generated by a formula - when the result of the formula is "", SumProduct has ...
- 3
0
votes
0
answers
73
views
How do I correctly use a dynamic array in VBA to avoid the Expected Sub, Function or Property error?
Having trouble getting code to compile due to an array name throwing the Expected Sub, Function or Property error.
Function is to take two input ranges, one of strings and one of times.
Strings are ...
- 13
0
votes
1
answer
88
views
Why is this UBound not identifying the correct limit?
I am new to using VBA within excel and am trying to implement a function to perform an algebraic operation on an array of numbers. This function requires creation of sub arrays with sizes relative to ...
1
vote
2
answers
250
views
VBA reDim Preserve 2D Arrays keep failling
I've read some posts saying that you can only reDim the last dimention of a multi-dimensional array in VBA.
Is there any work around for such a case?
Sub test()
Dim arr As Variant
Dim i As ...
- 911
3
votes
2
answers
2k
views
Check for/Detect Dynamic Array Spill in Excel
I want to check for/detect cells that contain values spilled from a dynamic array.
Let's say I have data in the cells A1:A10 and I write the formula =A1:A10 in the cell B1, then B1 itself will contain ...
- 823
0
votes
0
answers
348
views
Put Data into a dynamic array in VBA and split string into key value pairs
I have some raw data below which I am trying to format in Excel Spreadsheet.
The Data is formated as below
I need to format this data in VBA to where it looks like this.
The last row, "CTP ...
0
votes
2
answers
1k
views
VBA to insert or delete rows in named range based on spill range
Cross posted at: https://www.mrexcel.com/board/threads/vba-to-insert-or-delete-rows-in-named-range-based-on-spill-range.1214814/#post-5937281
I have a named range "nameList" (B3:E20) that ...
- 25
0
votes
1
answer
202
views
VBA errors with dynamic arrays
I am a beginner in VBA. I am trying to write a macro that checks if there is any missing data in several files. If data is missing, it would add the name of the file to an array, and at the end it ...
- 1
0
votes
1
answer
106
views
Storing SeriesCollection Values in dynamical array
I have the following issue: I create a chart from values that a user will insert in specific cells of a spreadsheet and the chart displays what I expect (The series are collected in the right way). ...
- 23
0
votes
0
answers
147
views
Clear ComboBox Containig an Array VBA and Repopulate with Column Index
I have a sigle column ComboBox that I have populated with a dynamic array; however, I'd like the code to replace the value of the combo box with respective column from my ListBox.
'Prepares the Active ...
5
votes
5
answers
2k
views
Excel function to construct an array from values without VBA
NB This question refers to some features like optional LAMBDA arguments and the ISOMITTED function which are only available (at the time of writing) in the Beta channel (more info here)
I am ...
- 5,553
0
votes
1
answer
2k
views
Excel Text to Columns in VBA with variable number of delimiters in each row?
I am processing instructor evaluations for a college in an Excel file. One row represents one course section and contains all student comments for that section. The number of comments may be anywhere ...
- 5
3
votes
3
answers
413
views
Excel UDF to Unpivot (Melt, Reverse pivot, Flatten, Normalize) blocks of data within Tables
This question will seek multiple approaches LET/LAMBDA VBA UDF and Power Query Function, so there will be no single right answer, but a solicitation of approaches to be used as references.
Scott ...
- 3,332