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)

 

[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

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

Does it also work with these lines of code by adding the const init = 20?

 

[Aberdham]

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

does this also work with the code const init 20?

 

[bobsan42]

does this also work with the code const init 20?

The functions as i posted them don't need it.
Instead you provide it when calling the function:

Powerarray(20,5,0.2)

This line is only necessary after you modify the function declaration and remove the first parameter.
Read carefully everything I wrote before, including the green comments in the code, and hopefully it will be clear.