Automatically run a macro/function/Subprocedure

[cyrus1979]

HI, I got a problem on the invoking of macro. What I have done is just design a function which is shown in the following:

-----------------------------------------------------------
Function update()

Dim I As Integer
Dim Lorry_number As Integer
Dim J As Integer
Dim K As Integer
Dim TRIPS(1 To 100) As Integer
Dim Same As Boolean

Dim Count As Integer
Dim Column As Integer
Dim Row As Integer
Dim M As Integer
Dim N As Integer
Dim Temp As Double


'Initialise the array TRIPS(K) and give the value 0 to each element
For K = 1 To 100
TRIPS(K) = 0
Next K

'Fill in "Validate the model" table
For I = 1 To 197
Lorry_number = Cells(I + 10, 19)
Same = False

'In the second time, the same lorry is found in the records,
'scan the relevant row in "Validate the model" according to the lorry number,avoid the same dispatch time record
If TRIPS(Lorry_number) > 0 Then
For K = 1 To TRIPS(Lorry_number)
If Cells(I + 10, 27) = Cells(Lorry_number + 10, 93 + 2 * (K - 1)) Then
Same = True

If Same Then
Exit For
End If

End If
Next K
End If


'if the record found is different from those in the relevant row in "Validate the model" table,
'fill in the dispatch time and the arrival time in the relevant cells
If Not Same Then
'If Cells(I + 10, 26) <> Cells(Lorry_number + 10, 93 + (2 * (TRIPS(Lorry_number) - 1))) Then
Cells(Lorry_number + 10, 93 + 2 * TRIPS(Lorry_number)) = Cells(I + 10, 27)
Cells(Lorry_number + 10, 94 + 2 * TRIPS(Lorry_number)) = Cells(I + 10, 31)
TRIPS(Lorry_number) = TRIPS(Lorry_number) + 1
'End If
End If
Next I


'Bubble sorting for the "validate the model" table, make sure each dispatch time and arrival time in ascending order

For Row = 1 To 51
Count = 0
Column = 93

'Count non-empty cells for each row
Do While Cells(Row + 10, Column) <> ""
Count = Count + 1
Column = Column + 1
Loop



'Start the sorting at least 2 pairs of dispatch time and arrival time found
If Count > 2 Then
For M = 1 To Count - 3 Step 2
For N = M + 2 To Count - 1 Step 2
If Cells(Row + 10, M + 92) > Cells(Row + 10, N + 92) Then
'Swap dispatch time cells
Temp = Cells(Row + 10, N + 92)
Cells(Row + 10, N + 92) = Cells(Row + 10, M + 92)
Cells(Row + 10, M + 92) = Temp

'Swap arrival time cells
Temp = Cells(Row + 10, N + 93)
Cells(Row + 10, N + 93) = Cells(Row + 10, M + 93)
Cells(Row + 10, M + 93) = Temp
End If
Next N
Next M
End If

Next Row

End Function

-----------------------------------------------------------
at the same time, I need to use Evolver 4.0.5 to solve the problem. I have deburged the code,but I havent found any problem in the code. However, when I put "=update()"in one of the blank cell, and click the tick let the VBA run, but it is not working properly. Can anyone give any help on this aspect?

 

[xlbo]

you do not "Run" a function embedded in a worksheet - it will run when a dependant cell is changed and thus a calculation is forced

Given a brief look at your code, this function cannot be used on a spreadsheet - you must call it from a sub (or make it a sub itself)

Sub Run_Update()
Update
End Sub

"Play" that sub to see the function working

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cyrus1979]

Hi,RGDS,GEOFF

Do I need to type"=Run_Update()" in any of the cell in the relevant spreadsheet?

Many thanks

Cyrus

 

[cyrus1979]

Hi,RGDS,GEOFF
It is not working. I have tried to use "Private Sub Worksheet_Change(ByVal Target As Range)", when I type"=worksheet_change($AA$11:$AA$207)",it is running again and again. I can just use "esc" to stop it .In this case, I can not run the software Evolver properly, it prompt"running error '91',Object variable or With block variable not set" bacause when I put "=worksheet_change($AA$11:$AA$207)" on the spreadsheet. but it's not happened when I put the previous function "=update()"in the cell. very confusing.

CYrus

 

[xlbo]

Your function does not return a result.

It therefore cannot be run by placing it on a worksheet

The reason it keeps running when you use it in the CHANGE sub is that it changes some data so the event is triggered again - going into an infinite loop

To run your function

1: Create a SUB as per my last post - copy all of the following:

Sub Run_Update()
Update
End Sub

into the same module you have your function in

2: Go back to your spreadsheet
3: Follow menu options Tools>Macros>Play macro
4: Choose "Run_Update" from the list

The Run_Update sub is how you call your function

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[cyrus1979]

HI,RGDS,GEOFF

I put this on my code view window which is from Sheet5, not from the module.

--------------------
Function Update()
my code
End Function

Sub Run_Update()
Update
End Sub

-----------------

when I do this,the compiler said "compiler error, no =" and high light the "Update" in the code. Actually, the Evolver can do the iteration when Evolver is running,I dont want to run follow Tool>Macros>play, because I need this function run automatically when any of the cells in $AA$11:$AA$207 column is changed by each iteration of the Evolver.

Many thanks

CYrus

 

[fumei]

While functions can run multiple instructions, general good practice is that Functions return a result. A single result. A Function = something....whatever that is.

Subs run multiple instructions.

If you want the code to run automatically perhaps, as Geoff suggested, you should have your code as a Sub.

Why do you have it as a function?

Gerry

 

[cyrus1979]

Hi,GERRY

I just wondering which I can choose between function and subs. However, I use Function to attempt what I want to do. I am not that good in using VBA. I am wondering how I can compose the code that can be automatically executed, at the same time, the data in $AA$11:$AA$207 would be changed by the Evolver in each iteration.THerefore, I can not run the macro by click the tool bar. In case I put the following in the code view window:
--------------------
Function Update()
my code
End Function

Sub Run_Update()
Update
End Sub

-----------------

THere is a compilation error happened: "a lack of: =". very confusing.

On the other hand,I did try to declare a return value function:
----------------------
Function Update() As integer
my code
Update()=3
End Function

------------------
And I just put it straight in the cell "=update()", it comes " #Name!"

but it is not working still.

Cyrus