0

I am trying to create a function for excel (2016) using visual basic for application(7.1), and part of the equation includes a summation including 5 empirical coefficients that will vary depending on which of the 6 conditions applies. I thought that the best way to do so was to create a two-dimensional array.

I'm having trouble populating it. I would use this formula often, and want to store the function in my PERSONAL.XLSB. As a result, I cannot populate it through an excel sheet and loop through data. Since the values in the array are empirical, I do not think it is suitable to use loops in VBA to populate.

Public Function Efficiency`

Dim CF As Variant
CF = (Array(Array(0.259, -0.00000495, 0.0000000139, -0.00000000000281, 3.77E-17), _
            Array(0.244, 0.00000367, 0.00000000871, -0.00000000000131, 1.5E-16), _
            Array(0.252, -0.000000641, 0.0000000113, -0.00000000000207, -5.49E-17), _
            Array(0.244, 0.00000337, 0.00000000889, -0.00000000000136, -1.44E-16), _
            Array(0.266, -0.00000776, 0.0000000158, -0.00000000000342, 1.22E-16), _
            Array(0.25, 0.000000177, 0.0000000108, -0.00000000000192, 7.3E-17)))
'EDIT: added rest of code
Dim Efficiency_counter: Efficiency_counter=0
For i = 0 to 4
Efficiency_counter = 100*(CSng(CF(Condition, i)))+ Efficiency_counter
Next i
Efficiency = 100-Efficiency_counter
End Function

When I debug, the formula results in #VALUE and CF(0,0) results in a <subscript out of range> error. If I change the variable type to numeric, the function displays the warning

Compile Error: expected array

Community
  • 1
  • 1
robreynolds88
  • 1
  • 2
  • 1
    Try `CF(0)(0)` it is an array of arrays not a two dimensional array. – Scott Craner Jul 25 '17 at 20:45
  • `CF(Condition)(i)` – Scott Craner Jul 25 '17 at 20:58
  • That worked! thank you. I edited my code to show the formula that I was using. – robreynolds88 Jul 25 '17 at 20:59
  • Possible duplicate of [How to initialize a multidimensional array variable in vba for excel](https://stackoverflow.com/questions/24584088/how-to-initialize-a-multidimensional-array-variable-in-vba-for-excel) Especially refer to the answer which shows how to use `varData = [{1, 2, 3; 4, 5, 6; 7, 8, 9}]` – YowE3K Jul 25 '17 at 21:02
  • 1
    Actually, the solution I just suggested won't work in your case - you will exceed the 256(?) character limit that that syntax allows. Pity. – YowE3K Jul 25 '17 at 21:09
  • Why not using a sheet? You can easily change your numbers and every time you are using your macro, you can create an array just by using `arr=rng.value` – Ibo Jul 25 '17 at 23:22

0 Answers0