Performing operations on items within an array - VBA

Question

Sorry for this question, but I haven’t found the answer in any of the texts or sites I’ve been researching. I am trying to do something that seems like it should be easy, but I don’t understand enough about arrays to pull it off. I am trying to create an array that is some number of rows; let’s say 10 rows, by 3 columns, or Myarr(1 to 10 , 1 to 3) – and then populate it as follows in memory before pasting it back into an excel sheet. Here’s an example using very simple constants and functions, not the ones I really need to run.

The reason is that I've found that running my particular construct as set of Excel formulas and VBA custom functions is very slow and results in a recalculation problem that I have written about in this forum that is not yet solved, so I am trying a work-around that performs all operations in an array, and then just pastes the result back to Excel.

Column 1 is just the list of numbers 1 to 10

Column 2 is the value of the previous row of Column 2 plus a constant; “Constant”; this is the part I really am puzzled by

Column 3 is just a function of the value of this row of Column 2

For example: Constant = 2 Function of Column 2 value is simply Column 2 value x 4

So the output should be Value col 1, previous value col 2 + Constant, column 2 x 4 as follows:

1,2,8

2,4,16

3,6,24

4,8,32

5,10,40

6,12,48

7,14,56

8,16,64

9,18,72

10,20,80

I just cant find any instructions about how to refer backwards to previous row values in an array and use them to produce a new value for that same column,

The simplest example would be a 1 dimensional array making a list of numbers where you started with a number and each successive row was the previous value + 1.

I realize this is probably basic stuff, but I must be searching on the wrong term to find an answer so I turn to you. Thank you very much for your help.

doesn't sound like that would solve the general problem with the slow UDFs. Might be easier to switch to Manual Calculation mode stackoverflow.com/documentation/excel-vba/1107/… — Slai
– Slai, Commented Jan 28, 2017 at 23:45

h2so4 · Accepted Answer · 2017-01-28 23:34:19Z

3

Did you try something like

Myarr(i,2)=Myarr(i-1,2)+const

answered Jan 28, 2017 at 23:34

h2so4

1,5771 gold badge10 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

joshuad Over a year ago

Thanks h2so4! I certainly will try it.

joshuad Over a year ago

Having syntax problems. What is wrong with this code? Sub newsub() Dim myarr(0 To 9, 0 To 2) As Variant Dim i As Integer, j As Integer For i = LBound(myarr, 0) To UBound(myarr, 0) j = myarr(i, 0) = myarr(i - 1, 0) + 2 Next i Debug.Print i, j End Sub

A.S.H Over a year ago

@joshuad the dimensions indices are 1-based. Use LBound(myarr, 1), UBound(myarr, 1), LBound(myarr, 2) and UBound(myarr, 2),

joshuad Over a year ago

I'm afraid this still isnt working, but not sure why. Sub newsub() Dim myarr(1 To 9, 0 To 2) As Variant Dim i As Integer, j As Integer For i = LBound(myarr, 1) To UBound(myarr, 1) j = myarr(i - 1, 2) + 2 Next i Debug.Print i, j End Sub Sorry!

A.S.H Over a year ago

That's because myarr(i-1, 2) is out of bounds when i = LBound(myarr, 1). You should start the iteration at For i = LBound(myarr, 1) +1 to .... And btw better Dim myarr(1 To 10, 1 To 3).

|

Collectives™ on Stack Overflow

Performing operations on items within an array - VBA

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related