Re: using arrays with loop statements

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

Hi Stefan
Have had a fiddle with the code.Two things.
Firstly:
I had in mind each array to be 2 dimensional, firstly number of items,
secondly size of the items. Have tried changing the code to allow for the 2
dimensions but with either 1 or 2 dimensions and depending on how/where I
have effected the delarations it returns either "0,0" or simply parrots Na
and Nb
Secondly:
and presumably related to why I am getting wrong outcomes, I haven't quite
understood specifically where (and the values) to put the declarations and
why you put Ll = 590 etc. at the initialise calculation comment. In the
General Declarations section I put


'Set the commencing index of an Array at 1 rather than the default of Zero
Option Base 1

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 'declares the dynamic array for Portrait objects
Dim B() As Long 'declares the dynamic array for Landscape objects

and

Dim CountA As Long
Dim CountB As Long
Dim Na As Long ' number of elements in array A
Dim Nb As Long ' number of elements in array B

just before the intialisation of the arrays and initialiasation of
calculation (relative to the latter see my comment at "Secondly above".

My progress will now be slower by the way as I am back working on a project
for a few months starting Monday




"Grimwadec" wrote:

Thanks Stefan

Looks like exactly what I want for which I am most grateful....but its
nearly 11 PM here in OZ and I will digest and trial the code over the next
few day....

"Stefan Hoffmann" wrote:

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: Handling ubound on an uninitialised array
    ... Function ArrDim(vArr As Variant) As Integer ... Dim i As Long, x As Long ... > I believe that Tushar's comment re an UPPERBOUND of -1 may relate> to some functions like split/filter or a scripting dictionary's items> array which return an array if no results were found. ... > I've just written following function which gives the DIMENSIONS of an> array. ...
    (microsoft.public.excel.programming)
  • 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: help with arrays
    ... >>Dim i As Integer ... >>You spreadsheet has two dimensions; so does the array. ... >>> DrAcctAmt, CrAcctAmt, DrAcctNo, CrAcctNo, Descr) ...
    (microsoft.public.excel.programming)
  • 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)