VBA help needed

[Aberdham]

Hi guys, I need some help with this,

Function powerarray(n, d)
Dim a()
ReDim a(1 To n, 1 To n)

( I am supposed to fill this part to get the desired result)

powerarray = a
End Function

Thank you in advance!
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
so basically powerarray(n,d) function generates n * n array that should contains the numbers as follow:

• The initial offer is 20
• Each cell down should be divided by (1+d)
• Each cell down to the right should be multiplied by (1+d)
• powerarray (5,0,20) should generate the following outcome:

20,00(initialoffer)
16,67 (= 20/1,2)	24,00 (= 20*1,2)​
13,89 (= 16,67/1,2)	20,00 (= 24/1,2)	28,80 (=24*1,2)	​
11,57 (= 13,89/1,2)	16,67 (=20/1,2)	24,00(=28,8/1,2)	34,56(=28,80*1,2)​
9,65 (= 11,57/1,2)	13,89 (= 16,67/1,2)	20,00(=24/1,2)	28,80(=34,56/1,2)	41,47(=34,56*1,2)

 

[bobsan42]

Try this:

'I added an additional parameter to allow providing initial value
Function PowerArray(ByVal init, ByVal n, ByVal d)
    'init is the value of the first element / top-left corner
    'n is the array size (n x n)
    'd is the number to use for math operations
    
    Dim a()
    ReDim a(1 To n, 1 To n)
    Dim i As Long, j As Long

    '####################################
    'this part is not mandatory, but otherwise the SPILLED formula result produces 0's for the empty elements
    For i = 1 To n
        For j = 1 To n
            a(i, j) = vbNullString
        Next j
    Next i
    '####################################

    a(1, 1) = init 'set the first element to the initial value
    d = 1 + d 'change d so we dont have to recalculate for each element
    If Not (n > 1) Then GoTo ep 'we don't need to continue if n is not at least 2
    
    'Start filling the array elements
    For i = 2 To n
        'Above the diagonal all elements are empty
        'Fill the elements under the main diagonal
        For j = 1 To i - 1
            a(i, j) = a(i - 1, j) / d
        Next j
        'Fill the elements on the diagonal
        a(i, i) = a(i - 1, i - 1) * d
    Next i
    
ep:
    PowerArray = a
    Erase a
End Function

 

[bobsan42]

And another version - slightly different, maybe a bit more straight-forward and logical. Maybe inperceptibly slower but same result

Function PowerArray_v2(ByVal init, ByVal n, ByVal d)
    'init is the value of the first element / top-left corner
    'n is the array size (n x n)
    'd is the number to use for math operations
   
    Dim a()
    ReDim a(1 To n, 1 To n)
    Dim i As Long, j As Long

    d = 1 + d 'change d so we dont have to recalculate for each element
   
    'Start filling the array elements
    For i = 1 To n
        For j = 1 To n
            Select Case j
                Case i 'Fill the elements on the main diagonal
                    If i > 1 Then
                        a(i, i) = a(i - 1, i - 1) * d
                    Else
                        a(i, i) = init 'set the first element to the initial value
                    End If
                Case Is > i 'Fill the elements above main the diagonal
                    a(i, j) = vbNullString 'Above the diagonal all elements are empty
                Case Is < i 'Fill the elements under the main diagonal
                    a(i, j) = a(i - 1, j) / d
            End Select
        Next j
    Next i
   
    PowerArray_v2 = a
    Erase a
End Function

 

[Aberdham]

And another version - slightly different, maybe a bit more straight-forward and logical. Maybe inperceptibly slower but same result

Function PowerArray_v2(ByVal init, ByVal n, ByVal d)
    'init is the value of the first element / top-left corner
    'n is the array size (n x n)
    'd is the number to use for math operations
  
    Dim a()
    ReDim a(1 To n, 1 To n)
    Dim i As Long, j As Long

    d = 1 + d 'change d so we dont have to recalculate for each element
  
    'Start filling the array elements
    For i = 1 To n
        For j = 1 To n
            Select Case j
                Case i 'Fill the elements on the main diagonal
                    If i > 1 Then
                        a(i, i) = a(i - 1, i - 1) * d
                    Else
                        a(i, i) = init 'set the first element to the initial value
                    End If
                Case Is > i 'Fill the elements above main the diagonal
                    a(i, j) = vbNullString 'Above the diagonal all elements are empty
                Case Is < i 'Fill the elements under the main diagonal
                    a(i, j) = a(i - 1, j) / d
            End Select
        Next j
    Next i
  
    PowerArray_v2 = a
    Erase a
End Function

Hey Bobsan42, Thanks a lot for your help. however I am getting the following result when I run the code.

the initial value is not there and the rest of the numbers are not doing the mathematical operation, any ideas on how this can be fixed?

thank you in advance!

 

[bobsan42]

Hey Bobsan42, Thanks a lot for your help. however I am getting the following result when I run the code.

the initial value is not there and the rest of the numbers are not doing the mathematical operation, any ideas on how this can be fixed?

thank you in advance!
View attachment 46614

Read the comments in the code (the green lines).
I added another parameter to the function - the initial value.
So the function now has 3 parameters. The initial value is the first one, the other 2 are the ones you requested.

 

[Aberdham]

Read the comments in the code (the green lines).
I added another parameter to the function - the initial value.
So the function now has 3 parameters. The initial value is the first one, the other 2 are the ones you requested.

the command are locked in this line Function PowerArray_v2(ByVal init, ByVal n, ByVal d)
I can't seem to change anything on that line of code, is there another way to get it done without touching that line of command?
Thank you

 

[bobsan42]

the command are locked in this line Function PowerArray_v2(ByVal init, ByVal n, ByVal d)
I can't seem to change anything on that line of code, is there another way to get it done without touching that line of command?
Thank you

I don't understand what you mean. Does the function produce a Runtime error? it should work flawlessly.

See the result I get below:

MrExcel Playbook 02 2021-09.xlsm
	A	B	C	D	E	F	G	H
1				PowerArray
2	initial value:	20		20
3	n	5		16.66667	24
4	d	0.2		13.88889	20	28.8
5				11.57407	16.66667	24	34.56
6				9.645062	13.88889	20	28.8	41.472
7
8
9				PowerArray_v2
10				20
11				16.66667	24
12				13.88889	20	28.8
13				11.57407	16.66667	24	34.56
14				9.645062	13.88889	20	28.8	41.472
Aberdham

Cell Formulas
Range	Formula
D2:H6	D2	=PowerArray(B2,B3,B4)
D10:H14	D10	=PowerArray_v2(B2,B3,B4)
Dynamic array formulas.

 

[Aberdham]

I don't understand what you mean. Does the function produce a Runtime error? it should work flawlessly.

See the result I get below:

MrExcel Playbook 02 2021-09.xlsm
	A	B	C	D	E	F	G	H
1				PowerArray
2	initial value:	20		20
3	n	5		16.66667	24
4	d	0.2		13.88889	20	28.8
5				11.57407	16.66667	24	34.56
6				9.645062	13.88889	20	28.8	41.472
7
8
9				PowerArray_v2
10				20
11				16.66667	24
12				13.88889	20	28.8
13				11.57407	16.66667	24	34.56
14				9.645062	13.88889	20	28.8	41.472
Aberdham

Cell Formulas
Range	Formula
D2:H6	D2	=PowerArray(B2,B3,B4)
D10:H14	D10	=PowerArray_v2(B2,B3,B4)
Dynamic array formulas.

Sub exercise()
result = powerarray(5, 0.2)
arrayPrint (result)
End Sub


Function powerarray(n, d)
Dim a()
ReDim a(1 To n, 1 To n)
Dim i As Long, j As Long

d = 1 + d 'change d so we dont have to recalculate for each element

'Start filling the array elements
For i = 1 To n
For j = 1 To n
Select Case j
Case i 'Fill the elements on the main diagonal
If i > 1 Then
a(i, i) = a(i - 1, i - 1) * d
Else
a(i, i) = init 'set the first element to the initial value
End If
Case Is > i 'Fill the elements above main the diagonal
a(i, j) = vbNullString 'Above the diagonal all elements are empty
Case Is < i 'Fill the elements under the main diagonal
a(i, j) = a(i - 1, j) / d
End Select
Next j
Next i


powerarray = a
Erase a
End Function



I tried this, and it gave me a bunch of 0?

I am wondering where did I get wrong?

 

[bobsan42]

you have changed my function definition and removed the initial value parameter INIT, so it is always zero or Null rather.
add a line under d=1+d:

d = 1 + d 'change d so we dont have to recalculate for each element
const init = 20

 

[Aberdham]

you have changed my function definition and removed the initial value parameter INIT, so it is always zero or Null rather.
add a line under d=1+d:

d = 1 + d 'change d so we dont have to recalculate for each element
const init = 20

ah great that works like a charm. thanks a lot!