Minimum Value

[surya181290]

Dear all
I am trying to find the minimum value in the array Stress(i,j)
The maximum value appears correct. However the minimum value always appears zero if all the elements are positive.

Kindly help



I am posting the code below

Private Sub CommandButton1_Click()
Dim i As Integer
Dim length As Double
Dim width As Double
Dim depth As Double
Dim division As Integer
Dim j As Integer
Dim CGX As Double
Dim CGY As Double
Dim k As Integer
length = Cells(1, 2).Value
width = Cells(2, 2).Value
depth = Cells(3, 2).Value
division = Range("b4").Value
ReDim area(division, division) As Double
For i = 1 To division
For j = 1 To division
area(i, j) = (length / division) * (width / division)
Next j
Next i
ReDim elemX(division, division) As Double
ReDim elemY(division, division) As Double
For i = 1 To division
For j = 1 To division
elemX(i, j) = length / (2 * division) + length * (i - 1) / (division)
elemY(i, j) = width / (2 * division) + width * (j - 1) / division
Next j
Next i
ReDim momareaX(division, division) As Double
ReDim momareaY(division, division) As Double
For i = 1 To division
For j = 1 To division
momareaX(i, j) = area(i, j) * elemX(i, j)
momareaY(i, j) = area(i, j) * elemY(i, j)
Next j
Next i
Cells(7, 2).Value = (WorksheetFunction.Sum(momareaX)) / WorksheetFunction.Sum(area)
Cells(8, 2).Value = (WorksheetFunction.Sum(momareaY)) / WorksheetFunction.Sum(area)
ReDim X(division, division) As Double
ReDim Y(division, division) As Double
For i = 1 To division
For j = 1 To division
X(i, j) = (WorksheetFunction.Sum(momareaX)) / WorksheetFunction.Sum(area)
Y(i, j) = (WorksheetFunction.Sum(momareaY)) / WorksheetFunction.Sum(area)
Next j
Next i
ReDim elemR(division, division) As Double
For i = 1 To division
For j = 1 To division
elemR(i, j) = ((elemX(i, j) - X(i, j)) ^ 2 + (elemY(i, j) - Y(i, j)) ^ 2) ^ 0.5
Next j
Next i
sumR = WorksheetFunction.Sum(elemR)
sumA = WorksheetFunction.Sum(area)
ReDim Xsquare(division, division) As Double
ReDim Ysquare(division, division) As Double
For i = 1 To division
For j = 1 To division
Xsquare(i, j) = (elemX(i, j) - Cells(7, 2).Value) * (elemX(i, j) - Cells(7, 2).Value)
Ysquare(i, j) = (elemY(i, j) - Cells(8, 2).Value) * (elemY(i, j) - Cells(8, 2).Value)
Next j
Next i
sumX = WorksheetFunction.Sum(Xsquare)
sumY = WorksheetFunction.Sum(Ysquare)
ReDim elemIx(division, division) As Double
ReDim elemIy(division, division) As Double
For i = 1 To division
For j = 1 To division
elemIx(i, j) = (area(i, j) * area(i, j)) / 12 + area(i, j) * Ysquare(i, j)
elemIy(i, j) = (area(i, j) * area(i, j)) / 12 + area(i, j) * Xsquare(i, j)
Next j
Next i


Ix = WorksheetFunction.Sum(elemIx)
Iy = WorksheetFunction.Sum(elemIy)
Cells(6, 1).Value = Ix
Cells(6, 2).Value = Iy
ReDim stress(division, division) As Double
ReDim axial(division, division) As Double
ReDim moment(division, division) As Double
ReDim Pelem(division, division) As Double
ReDim Mxelem(division, division) As Double
ReDim Myelem(division, division) As Double


   k = 1
For i = 1 To division
For j = 1 To division
Pelem(i, j) = (-1000 * Cells(14 + k, 5).Value) / (division * division * area(i, j))
Mxelem(i, j) = (1000000 * Cells(14 + k, 11).Value) * ((elemY(i, j)) - Cells(8, 2).Value) / Ix
Myelem(i, j) = (1000000 * Cells(14 + k, 12).Value) * ((elemX(i, j)) - Cells(7, 2).Value) / Iy
stress(i, j) = (Pelem(i, j) + Mxelem(i, j) + Myelem(i, j))
Cells(100 + i, 100 + j).Value = stress(i, j)
Next j
Next i




Cells(14 + k, 15).Value = WorksheetFunction.Min(stress)
Cells(14 + k, 16).Value = WorksheetFunction.Max(stress)
















End Sub




Private Sub CommandButton2_Click()
Range("b1:bbb10000").ClearContents
End Sub

 

[joeu2004]

For a quick-and-dirty solution, enter the statement Option Base 1 at the beginning of the module.

When you enter statements of the form ReDim stress(division, division) As Double, the lower bounds are implicitly the optionBase value, which is zero by default. In other words, it is equivalent to ReDim stress(0 to division, 0 to division). Since the array type is Double, not Variant, the initial value of stress(0,0) is zero, not empty. Thus, the Min function might indeed see zero as the smallest value.

IMHO, instead of setting Option Base 1, it would be better to explicitly define the lower as well as upper bounds of the arrays. In other words, change the array declarations to the form stress(1 to division, 1 to division) As Double.

That gives you the flexibility (in the future) of having different lower bounds for some arrays. And it avoids mistakes if you later change the Option Base without realizing the implicit consequences.

-----
PS, unrelated observation.... Change type Integer to type Long. Even if it is unlikely that any integer variable will exceed 32767, using type Long avoids surprises. For example, the following results in an overflow error, even though the value of "i" does not exceed 32767.

Dim i As Integer
i = i + 32767 + 1 - 1
MsgBox i

 

[surya181290]

For a quick-and-dirty solution, enter the statement Option Base 1 at the beginning of the module.

When you enter statements of the form ReDim stress(division, division) As Double, the lower bounds are implicitly the optionBase value, which is zero by default. In other words, it is equivalent to ReDim stress(0 to division, 0 to division). Since the array type is Double, not Variant, the initial value of stress(0,0) is zero, not empty. Thus, the Min function might indeed see zero as the smallest value.

IMHO, instead of setting Option Base 1, it would be better to explicitly define the lower as well as upper bounds of the arrays. In other words, change the array declarations to the form stress(1 to division, 1 to division) As Double.

That gives you the flexibility (in the future) of having different lower bounds for some arrays. And it avoids mistakes if you later change the Option Base without realizing the implicit consequences.

-----
PS, unrelated observation.... Change type Integer to type Long. Even if it is unlikely that any integer variable will exceed 32767, using type Long avoids surprises. For example, the following results in an overflow error, even though the value of "i" does not exceed 32767.

Dim i As Integer
i = i + 32767 + 1 - 1
MsgBox i

thanks joeu2004.

I am new to VBA.
I am trying to write a small code for a civil engineering problem.
Hope to ask you a few more queries as I move along