Re: using arrays with loop statements

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

hi,

Grimwadec wrote:
Thanks Stefan...looks like I am out of my depth here...I don't understand the range definitions of closed, half-open or open interval ?
open: Ll < Na * Sa + Nb * Sb < Lu
No boundary is part of the range.

half-open: Ll < Na * Sa + Nb * Sb <= Lu or
Ll <= Na * Sa + Nb * Sb < Lu
One boundary is part of the range.

closed: Ll <= Na * Sa + Nb * Sb <= Lu
Both boundaries are part of the range.


what I am aiming for is the matrix result to be BOTH equal to or greater than 590 AND equal to or less than 600..
It is a closed range, because both boundaries are a solution to your problem. So the <= operator is necessary for both checks.

as it transpires I don't think I really need to understand it but it would be useful to know how to effect the transformation to use it for computation in excel even though I have been able to transform it for Access purposes, and with some further thought as to how to write the Loop statement code in VBA (for Access purposes) I think I now know how to approach writing that code...the whole exercise by the way, is for a database that my nephew will use in his business for formulating the most efficient means of setting out objects for printing (by a new machine he is getting)on a continuous roll of material 600mm wide.
I assume that it is better to have more large objects then small objects.

Dim Ll As Long ' lower boundary
Dim Lu As Long ' upper boundary
Dim Sa As Long ' size of elements in array A
Dim Sb As Long ' size of elements in array B

Dim A() As Long
Dim B() As Long

Public Function TestNumbers(Na As Long, Nb As Long) As Boolean

TestNumbers = ((Na * Sa) + (Nb * Sb)) >= Ll And _
((Na * Sa) + (Nb * Sb)) <= Lu

End Function

Public Sub CalcNumbers

Dim CountA As Long
Dim CountB As Long
Dim Na As Long
Dim Nb As Long

' initialize your arrays

' initialize calculation
Ll = 590
Lu = 600
Sa = 29
Sb = 15
Na = 0
Nb = 0

For CountA = LBound(A()) To UBound(A())
For CountB = LBound(B()) To UBound(B())
If TestNumbers(A(CountA), B(CountB)) Then
' output all solutions to the immediate window
Debug.? "Solution (" & A(CountA) & ", " & B(CountB) & ")."
If Sa > Sb Then
If A(CountA) > Na Then
Na = A(CountA)
Nb = B(CountB)
End If
Else
If B(CountA) > Nb Then
Na = A(CountA)
Nb = B(CountB)
End If
End If
End If
Next CountB
Next CountA

MsgBox "Best solution (" & Na & ", " & Nb & ")."

End Sub

mfG
--> stefan <--
.

Relevant Pages

  • Re: using arrays with loop statements
    ... dimensions but with either 1 or 2 dimensions and depending on how/where I ... 'Set the commencing index of an Array at 1 rather than the default of Zero ... Dim Ll As Long ' lower boundary ... Both boundaries are part of the range. ...
    (microsoft.public.access.formscoding)
  • Re: using arrays with loop statements
    ... Both boundaries are part of the range. ... Dim Ll As Long ' lower boundary ... Dim Sb As Long ' size of elements in array B ... Dim CountB As Long ...
    (microsoft.public.access.formscoding)
  • Re: Detecting a running process.
    ... Private Declare Function EnumProcessModules Lib "psapi.dll" _ ... (ByVal dwProcessID As Long, _ ... Dim nProcesses As Long ... 'fill an array of longs with the ...
    (microsoft.public.vb.winapi)
  • Re: Updated datestamp doesnt work
    ... Public Sub StoreMyOldVals ... ' store values of current row in array ... Dim dbs As DAO.Database, rst As DAO.Recordset ... Dim var As Variant ...
    (microsoft.public.access.gettingstarted)
  • Re: Updated datestamp doesnt work
    ... Public Sub StoreMyOldVals ... ' store values of current row in array ... Dim dbs As DAO.Database, rst As DAO.Recordset ... Dim var As Variant ...
    (microsoft.public.access.gettingstarted)