using arrays with loop statements

[Guest]

i have written a code that fills 2 dynamic arrays, each array consisting of a
derived number of integer elements and the derived integer dimension, e.g
array1 has 20 objects each 50 mm in size and array 2 has 10 objects each 35mm
in size. I want to be able to write code that will work out what matrix of
array 1 and array 2 elements fit within a given parameter, e.g between 590
and 600 mm. e,g. I know that say 7 array 1 objects (350mm) and 7 array2
objects (245mm) will equate to 595mm and fit within the parameter. I can't
work out how to write the Loop statement to do this. Can anyone help please?

 

[Stefan Hoffmann]

hi,

i have written a code that fills 2 dynamic arrays, each array consisting of a
derived number of integer elements and the derived integer dimension, e.g
array1 has 20 objects each 50 mm in size and array 2 has 10 objects each 35mm
in size. I want to be able to write code that will work out what matrix of
array 1 and array 2 elements fit within a given parameter, e.g between 590
and 600 mm. e,g. I know that say 7 array 1 objects (350mm) and 7 array2
objects (245mm) will equate to 595mm and fit within the parameter. I can't
work out how to write the Loop statement to do this. Can anyone help please?

This is called the "Knapsack problem". See

http://en.wikipedia.org/wiki/0/1_knapsack_problem

..

If your sizes in both arrays are constant over all the elements then it
is a simple equation:

Ll <= Na * Sa + Nb * Sb <= Lu

Ll, Lu lower and upper limit [mm]
Na, Nb number of elements in array
Sa, Sb size of elements in array [mm]

Solve it for Na and Nb.


mfG
--> stefan <--

 

[Guest]

Thanks Stefan but I would appreciate more help..I understand the equation but
not how to "Solv e it for Na and Nb" as you put it (I have no formal maths
training) perhaps when I undertsand the "Solve it" aspect I will then be
able to write the VBA code to do it but I tell you this latter aspect is not
apparent to me right now...Can you help further please

hi,

i have written a code that fills 2 dynamic arrays, each array consisting of a
derived number of integer elements and the derived integer dimension, e.g
array1 has 20 objects each 50 mm in size and array 2 has 10 objects each 35mm
in size. I want to be able to write code that will work out what matrix of
array 1 and array 2 elements fit within a given parameter, e.g between 590
and 600 mm. e,g. I know that say 7 array 1 objects (350mm) and 7 array2
objects (245mm) will equate to 595mm and fit within the parameter. I can't
work out how to write the Loop statement to do this. Can anyone help please?

This is called the "Knapsack problem". See

http://en.wikipedia.org/wiki/0/1_knapsack_problem

..

If your sizes in both arrays are constant over all the elements then it
is a simple equation:

Ll <= Na * Sa + Nb * Sb <= Lu

Ll, Lu lower and upper limit [mm]
Na, Nb number of elements in array
Sa, Sb size of elements in array [mm]

Solve it for Na and Nb.


mfG
--> stefan <--

 

[Guest]

OK...so I have had a bit more of a chance to look at the formula and have
tried it in an Excel cell thus =(Ll <= Na * Sa + Nb * Sb <= Lu) and
substituted the values thus =(590 <= 20*29 + 1*15 <= 600) and I get the
result FALSE when clearly 20*29(580) +1*15 (15) = 595 which falls between 590
and 600 therefore the result should be TRUE (I don't understand why both
comparison operators are <=, shouldn't one of them be >=).....Now in my more
familiar format of Access the following function does work with the following
returning TRUE (and if I substitute 30 for 29 it correctly returns FALSE

Function Trial()
If ((20 * 29) + (1 * 15)) >= 590 And ((20 * 29) + (1 * 15)) <= 600 Then
MsgBox "TRUE"
Else
MsgBox "FALSE"
End If
End Function

Thanks Stefan but I would appreciate more help..I understand the equation but
not how to "Solv e it for Na and Nb" as you put it (I have no formal maths
training) perhaps when I undertsand the "Solve it" aspect I will then be
able to write the VBA code to do it but I tell you this latter aspect is not
apparent to me right now...Can you help further please

hi,

i have written a code that fills 2 dynamic arrays, each array consisting of a
derived number of integer elements and the derived integer dimension, e.g
array1 has 20 objects each 50 mm in size and array 2 has 10 objects each 35mm
in size. I want to be able to write code that will work out what matrix of
array 1 and array 2 elements fit within a given parameter, e.g between 590
and 600 mm. e,g. I know that say 7 array 1 objects (350mm) and 7 array2
objects (245mm) will equate to 595mm and fit within the parameter. I can't
work out how to write the Loop statement to do this. Can anyone help please?

This is called the "Knapsack problem". See

http://en.wikipedia.org/wiki/0/1_knapsack_problem

..

If your sizes in both arrays are constant over all the elements then it
is a simple equation:

Ll <= Na * Sa + Nb * Sb <= Lu

Ll, Lu lower and upper limit [mm]
Na, Nb number of elements in array
Sa, Sb size of elements in array [mm]

Solve it for Na and Nb.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

OK...so I have had a bit more of a chance to look at the formula and have
tried it in an Excel cell thus =(Ll <= Na * Sa + Nb * Sb <= Lu) and
substituted the values thus =(590 <= 20*29 + 1*15 <= 600) and I get the
result FALSE when clearly 20*29(580) +1*15 (15) = 595 which falls between 590
and 600 therefore the result should be TRUE

The formula is a mathematical one. You can't use it for computation
without transformation.

(I don't understand why both
comparison operators are <=, shouldn't one of them be >=)

The choice of these operators depends on your problem: is the range of
590 to 600 a closed, half-open or open interval?

.....Now in my more
familiar format of Access the following function does work with the following
returning TRUE (and if I substitute 30 for 29 it correctly returns FALSE

Function Trial()
If ((20 * 29) + (1 * 15)) >= 590 And ((20 * 29) + (1 * 15)) <= 600 Then

This is a computational form of our formula.


mfG
--> stefan <--

 

[Guest]

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 ? 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 seems my lack of Maths skills prevents me understanding
your formula...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.

Thanks for your help

 

[Stefan Hoffmann]

hi,

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 <--

 

[Guest]

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....

 

[Guest]

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