Problem resizing arrays

[Mr Crown]

Hi

I wrote this makro, to calculate a surface-temperature as function of insulation-thickness. I want to collect all values of length and temperature in one Array each. And then make a plot of the result.

I make the calculations for a number of insulationthicknesses with a FOR-loop. Inside the FOR-loop I use a WHILE-loop to iterate the temperature. It all works as long as I specify a size of the Arryas in the beginning of the makro.

But I want the arrays to grow during the calculations. From size/length 1 to the size corresponding the numbers of temperatures/thicknessvalues when the caculations are finished. I dont want any empty cells in the arrays!

I use ”i” and ”j” as index for the arrays. And I try to use”ReDim Preserve” to change the sizes of the arrays.


When I in the beginning write
Dim MyArray() As Variant
Dim MyArray2() As Variant

I get the error ”Index is outside the interval” by ”MyArray(i) = L”



When I write:
Dim MyArray As Variant
Dim MyArray2 As Variant

I get the error ”Incompatible file type” by ”MyArray(i) = L”




When I write:
Dim MyArray(1 To 100) As Variant
Dim MyArray2(1 To 100) As Variant

it works, but I get empty cells in the array, depending on the number of thickness-values.



Anyone could helpo me solve this problem?


Here is the macro:



Sub Makro1()


'This works
Dim MyArray(1 To 100) As Variant
Dim MyArray2(1 To 100) As Variant

'This DOESNT works
'Dim MyArray() As Variant
'Dim MyArray2() As Variant


'This DOESNT either works
'Dim MyArray As Variant
'Dim MyArray2 As Variant



Dim L As Variant
Dim Ts_ber As Variant

Dim i As Integer
Dim j As Integer
Dim x As Variant
Dim y As Variant
Dim z As Variant
Dim q As Variant

Ti = 278
Tomg = 298
W_fonster = 0.65
H_fonster = 1
k_polyst = 0.03
x = 0.001 'Thinest insulation
y = 0.1 'Thickest insulation
i = 1
j = 1




'------------HERE THE CALCULATIONS STARTS----------------

For L = x To y Step 0.001

Ts_giss1 = Ti + 10
T_film = (Ts_giss1 + Tomg) / 2
k_luft = -0.0000000343 * (T_film ^ 2) + 0.000098216 * T_film - 0.00014045
Pr = 0.0000005536157 * (T_film ^ 2) - 0.0005812727 * T_film + 0.8325763
konst = 0.68857 * (T_film ^ 4) - 992.85 * (T_film ^ 3) + 541960 * (T_film ^ 2) - 133470000 * T_film + 12628000000#
Gr = konst * (Tomg - Ts_giss1)
Nu = (0.825 + (0.387 * (Pr * Gr) ^ (1 / 6) / (1 + (0.492 / Pr) ^ (9 / 16)) ^ (8 / 27))) ^ 2
h = k_luft * Nu / H_fonster
Ts_ber = L / k_polyst * (h * (Tomg - Ts_giss1) + 0.6 * 0.00000005687 * (Ts_giss1 + Tomg) * (Ts_giss1 ^ 2 + Tomg ^ 2) * (Tomg - Ts_giss1)) + 278
deltaT_1 = Ts_ber - Ts_giss1
Diff = 100

Ts_giss2 = Tomg - 3





'---------HERE THE ITERATIONS START----------

While Abs(Diff) > 0.0001

T_film = (Ts_giss2 + Tomg) / 2
k_luft = -0.0000000343 * (T_film ^ 2) + 0.000098216 * T_film - 0.00014045
Pr = 0.0000005536157 * (T_film ^ 2) - 0.0005812727 * T_film + 0.8325763
konst = 0.68857 * (T_film ^ 4) - 992.85 * (T_film ^ 3) + 541960 * (T_film ^ 2) - 133470000 * T_film + 12628000000#
Gr = konst * (Tomg - Ts_giss2)
Nu = (0.825 + (0.387 * (Pr * Gr) ^ (1 / 6) / (1 + (0.492 / Pr) ^ (9 / 16)) ^ (8 / 27))) ^ 2
h = k_luft * Nu / H_fonster
Ts_ber = L / k_polyst * (h * (Tomg - Ts_giss2) + 0.6 * 0.00000005687 * (Ts_giss2 + Tomg) * (Ts_giss2 ^ 2 + Tomg ^ 2) * (Tomg - Ts_giss2)) + 278
deltaT_2 = Ts_ber - Ts_giss2
T_temp = Ts_giss2
Ts_giss2 = Ts_giss1 - deltaT_1 * (Ts_giss2 - Ts_giss1) / (deltaT_2 - deltaT_1)
Ts_giss1 = T_temp
Diff = Abs(deltaT_2 - deltaT_1)
deltaT_1 = deltaT_2

Wend

'-----------ITERATIONS FINISHED------------



'TRYING TO COLLECT THE NEW RESULTS IN THE ARRAYS AFTER EACH INTERAITION
MyArray(i) = L
MyArray2(j) = Ts_ber
i = i + 1
j = j + 1


'I want to use "Redim Preserve" to set new size of the Arrays.

'ALTERNATIVE 1. DONT KNOW IF IT WORKS YET.
'ReDim Preserve MyArray(1 To i)
'ReDim Preserve MyArray2(1 To j)

'ALTERNATIVE 2. DONT KNOW IF IT WORKS YET.
'ReDim Preserve MyArray(UBound(MyArray) + 1)
'ReDim Preserve MyArray2((UBound(MyArray2) + 1))




Next L


'----------CALCULATIONS FINISHED---------------







'---------------MAKE A PLOT-------------------


If ActiveSheet.ChartObjects.Count > 0 Then
ActiveSheet.ChartObjects.Delete
End If

Dim shp As Shape
Dim cht As Chart
Dim srs As Series
Dim Yvals() As Variant

Set shp = ActiveSheet.Shapes.AddChart2(XlChartType:=xlLine)
Set cht = shp.Chart
Set srs = cht.SeriesCollection.NewSeries

cht.Parent.Name = "Chart 1"


With srs
.XValues = MyArray()
.Values = MyArray2()
End With


cht.AutoScaling = True
cht.ChartTitle.Text = "ts sfa L"
srs.Format.Line.Weight = 1.5






End Sub

[CK76]

You can use Redim Preserve to grow array. However, I wouldn't recommend it in most cases, as it will slowdown process etc.

When storing values of unknown length/count. You'd use some container, such as collection / dictionary.
You can then Redim your array variable based on count of items in the container and populate it as you wish.
Note: If using array to fill series. This can be done directly by using dictionary's item/key. dictionary.Items, dictionary.Keys are both single dimension array.
Ex:

Please Login or Register  to view this content.

[Mr Crown]

Thanks for tips/advice. Ill continue with that.

(Do you have any ideas why I get those errors?)

[CK76]

Please Login or Register  to view this content.

This you get error since you don't have any elements in array. You'll first need to set array elements (1 to x).

Please Login or Register  to view this content.

This you get error, since it's not even an array. So mismatch occurs. Variant, only declares variable that can hold any type (array, Long, String etc, i.e. unknown), but must be implicitly set to some data type.
Ex:

Please Login or Register  to view this content.

Edit: You may find following link useful.
https://excelmacromastery.com/excel-vba-array/

[Mr Crown]

Thanks for the answers and the link!