PDA

View Full Version : Please help with VBA project



progdude
09-17-2010, 11:08 AM
Hey guys, im a second year engineering student and really need help with my VBA project. Here is the question... Any help will be much appreciated! Thanks

 ------
Task in VBA -2010.
Imagine a large number of near-sighted birds who have excellent memories, hearing and spatial awareness (ruimtelike oriŽntasie). They are flying in search of the highest points of a forest of poles, planted in a two-dimensional space x,y. When you find the birds, they are flying each in a random xy position (x0i and y0i for bird number i, within a search space limited by xmin, xmax, ymin, ymax). Since each bird can only see its immediate surrounds, they rely on each other to find the highest point. Each second, the birds call out to each other and through their excellent hearing, they determine the xy position from where the highest call came from, which they call xG and yG.
Once they’ve located the position of the highest call, they change their velocity, based on two things. Stored in their excellent memories they have the xy position of their own best point visited so far (xBi and yBi for bird number i). Also they have xG and yG of the flock’s highest point at that specific time.
Assume their current velocity can be expressed as two vectors in the xy space. We’ll call these two velocities vxi and vyi for bird number i. Each bird also knows its position xi and yi. Using these variables they change their velocity according to the following formula:
vxi = vxi +a( xG - xi) + b(xBi - xi)
and vyi = vyi +a( yG - yi) + b(yBi - yi)
Assume all the variables are in metre and that the velocities are thus in m/s. The a and b variables are variables that have two components (userpref_a*random for a and userpref_b*random for b)
You need to determine the velocities and positions of every bird at one second intervals.
Typically you would stop the process after a predetermined time or when the solution does not improve very much over time.
Make teams of four students each. Study the problem above and then write a VB application that can find good solutions to any problem of the following form, where all the variables and the function values are read of a file "GetValueXYZ.xlsm". Use VB to send x, y and z values to the specific positions on the sheet. Read the Value from the column E.
You will use the logic of the birds above, but will expand the project to three dimensions XYZ. You must also hand in a report where you describe the problem, your program and its working. Your application should find the maximum and/or minimum value of the function Value.
A typical application will have an input form, some graphical representation of the search and an output form with Function values, as well as some information of interest to a user.
-----

Just to make it clear, I have a general understanding of the concept, but have NO idea of how to approach this. So an idea of what direction to go would be appreciated. :banghead:

Paul_Hossler
09-17-2010, 11:52 AM
One of the forum rules is "No Homework assignments"

I think people will be happy to help with an VBA aspect if you get stuck, but I think you're going to have to lay out the whole, entire, complete thing in pseudo-code and if you're stuck, ask a specific question

Paul

progdude
09-18-2010, 01:22 PM
Sure no problem.

Here is what Iv got after a 12hour session.

I get Runtime error 424 Object Required
on the line Iterations = Iterations.Value

For whatever reason, it doesnt like that bit of code.... (I have checked the userform and it is named correctly)

Any ideas?


------
Private Sub Activate_Click()
Dim Birds, Iterations, c, d, e, f, g As Integer
Iterations = Iterations.Value
Birds = NumberOfBirds.Value
Dim a, b, x, y, PosArr(99, 2), ValArr(99), Val, BestVal, BestValArr(9), BestPosArr(9, 2) As Long
a = userpref_a.Value * Rnd
b = userpref_b.Value * Rnd
For c = 1 To 10
For e = 0 To Birds - 1
PosArr(e, 0) = Rnd * 1000 - 500
PosArr(e, 1) = Rnd * 1000 - 500
PosArr(e, 2) = Rnd * 1000 - 500
Next
For d = 0 To Iterations - 1
For e = 0 To Birds - 1
x = PosArr(i, 0)
y = PosArr(i, 1)
Z = PosArr(i, 2)
Range("A1").Offset(k, 1).Value = x
Range("A1").Offset(k, 2).Value = y
Range("A1").Offset(k, 3).Value = Z
Next
Val = ValArr(0)
f = 0
If Max Then
For e = 1 To Birds - 1
If ValArr(e) > Val Then Val = ValArr(e): f = e
Next
ElseIf Min Then
For e = 1 To Birds - 1
If ValArr(e) < Val Then Val = ValArr(e): f = e
Next
End If
BestValArr(d) = Val
BestPosArr(d, 0) = PosArr(f, 0)
BestPosArr(d, 1) = PosArr(f, 1)
BestPosArr(d, 2) = PosArr(f, 2)
BestX = PosArr(0, 0)
BestY = PosArr(0, 1)
BestZ = PosArr(0, 2)
For e = 0 To Birds - 1
If PosArr(e, 0) > BestX Then BestX = PosArr(e, 0)
If PosArr(e, 1) > BestX Then BestY = PosArr(e, 1)
If PosArr(e, 2) > BestX Then BestZ = PosArr(e, 2)
PosArr(e, 0) = PosArr(e, 0) + a * (BestPosArr(d, 0) - PosArr(e, 0)) + b * (BestX - PosArr(e, 0))
PosArr(e, 1) = PosArr(e, 1) + a * (BestPosArr(d, 1) - PosArr(e, 1)) + b * (BestY - PosArr(e, 1))
PosArr(e, 2) = PosArr(e, 2) + a * (BestPosArr(d, 2) - PosArr(e, 2)) + b * (BestZ - PosArr(e, 2))
Next
Next
BestVal = BestValArr(0)
If Max Then
For d = 1 To Iterations - 1
If BestValArr(d) > BestVal Then BestVal = BestValArr(d): g = d
Next
ElseIf Min Then
For d = 1 To Iterations - 1
If BestValArr(d) > BestVal Then BestVal = BestValArr(d): g = d
Next
End If
Range("A1").Offset(c, 1).Value = BestPosArr(g, 0)
Range("A1").Offset(c, 2).Value = BestPosArr(g, 1)
Range("A1").Offset(c, 3).Value = BestPosArr(g, 2)
Next
End Sub

Paul_Hossler
09-18-2010, 04:33 PM
Dim Birds, Iterations, c, d, e, f, g As Integer
Iterations = Iterations.Value


The way Dim works is that all variable need an 'As .....' to be anything other than a variant

Since Iterations is Dim-ed as a Variant, you don't .Value, you just


Iterations = 1234


If Iterations is some kind of Object, a TextBox for instance, the .Value is the way. If Iterations is also on the Userform, then something like


UserForm1.Iterations.Value = 1234


Try just


Dim c as Long, d as Long, e as Long, f as Long, g As Long


and see
Paul