Just glancing through your code I noticed a good best practice.
Define your workbooks and worksheets.

Dim wb As Workbook
Dim wsCurr As Worksheet

Set wb = Application.Workbooks("MyWorkbook.xls")
Set wsCurr = wb.Worksheets("Sheet1")

So that later you can be sure you are pulling from the correct
source. A modification to your code:

Startrow = wb.wsCurr.Range(LotNoCell.Address).Row
StartlotColumn = wb.wsCurr.Range(LotNoCell.Address).Column
StartDataColumn = wb.wsCurr.Range(DataCell.Address).Column

Not sure that this is your problem, but it will help a lot if you have
multiple workbooks open.

Hope this helps.

On Feb 21, 10:25*am, Richard Wood
wrote:
Here is all the code. As I said this is not my strong point, I learnt a bit
of baisc over 20 years ago and have not done much with it since.

Function GetAllDataForLot(LotNoCell, DataCell)
* * MaskLen = 8
* * RowCount = 0
* * StartLotNoStr = ""
* * MylotNoStr = ""
* * sumof = 0

* * Startrow = Range(LotNoCell.Address).Row
* * StartlotColumn = Range(LotNoCell.Address).Column
* * StartDataColumn = Range(DataCell.Address).Column

* * n = 1

* * Do While (Left(Worksheets("BLCK Classified").Cells((Startrow + n - 1),
StartlotColumn).Value, MaskLen)) = (Left(Worksheets("BLCK
Classified").Cells(Startrow, StartlotColumn).Value, MaskLen))
* * n = n + 1
* * Loop

* * RowCount = n - 1

For c = 1 To RowCount
* * myrow = Startrow + c - 1
* * mycolumn = StartDataColumn

* * sumof = sumof + Worksheets("BLCK Classified").Cells(myrow, mycolumn).Value

Next c

GetAllDataForLot = sumof

End Function

Function RowOffset(LotNoCell)

* * MaskLen = 8
* * RowCount = 0

* * Startrow = Range(LotNoCell.Address).Row
* * StartlotColumn = Range(LotNoCell.Address).Column

* * n = 1

* * Do While (Left(Worksheets("BLCK Classified").Cells((Startrow + n - 1),
StartlotColumn).Value, MaskLen)) = (Left(Worksheets("BLCK
Classified").Cells(Startrow, StartlotColumn).Value, MaskLen))
* * n = n + 1
* * Loop

* * RowCount = n - 1

RowOffset = RowCount - 1

End Function
Function SmallestBK(Start1, Start2)
* * Column1 = Range(Start1.Address).Column
* * Column2 = Range(Start2.Address).Column
* * Startrow = Range(Start1.Address).Row

* * If Worksheets("BLCK Overview").Cells(Startrow, Column1).Value = "" Then
* * * * lowest = Worksheets("BLCK Overview").Cells(Startrow, Column2).Value
* * Else
* * lowest = Worksheets("BLCK Overview").Cells(Startrow, Column1).Value
* * End If

* * For n = 1 To 118
* * * * If Len(Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column1).Value) 5 Then
* * * * * * If Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column1).Value < lowest Then
* * * * * * lowest = Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column1).Value
* * * * * * End If
* * * * End If
* * Next n

* *For n = 1 To 118
* * * * If Len(Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column2).Value) 5 Then
* * * * * * * * If Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column2).Value < lowest Then
* * * * * * * * lowest = Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column2).Value
* * * * * * * * End If
* * * * End If
* * Next n

* * * * For c = 1 To 118
* * * * If Right(Worksheets("BLCK Overview").Cells(Startrow + c - 1,
Column2).Value, 1) = "A" Then
* * * * * * If Left(Worksheets("BLCK Overview").Cells(Startrow + c - 1,
Column2).Value, 8) = lowest Then
* * * * * * * * lowest = lowest + "A"
* * * * * * End If
* * * * End If
* * Next c

* * SmallestBK = lowest

End Function

Function NextSmallestBK(Start1, Start2, LastSmallest)
* * Column1 = Range(Start1.Address).Column
* * Column2 = Range(Start2.Address).Column
* * Startrow = Range(Start1.Address).Row
* * LastSRow = Range(LastSmallest.Address).Row
* * LastSColumn = Range(LastSmallest.Address).Column

* * lowest = "zzzzzzzz"

* * For n = 1 To 118
* * * * If Len(Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column1).Value) 5 Then
* * * * * * If Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column1).Value Worksheets("BLCK Overview").Cells(LastSRow,
LastSColumn).Value Then
* * * * * * * * If Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column1).Value < lowest Then
* * * * * * * * lowest = Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column1).Value
* * * * * * * * End If
* * * * * * End If
* * * * End If
* * Next n

* *For n = 1 To 118
* * * * If Len(Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column2).Value) 5 Then
* * * * * * If Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column2).Value Worksheets("BLCK Overview").Cells(LastSRow,
LastSColumn).Value Then
* * * * * * * * If Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column2).Value < lowest Then
* * * * * * * * lowest = Worksheets("BLCK Overview").Cells(Startrow + n - 1,
Column2).Value

* * * * * * * * End If
* * * * * * End If
* * * * End If
* * Next n

* * For c = 1 To 118
* * * * If Right(Worksheets("BLCK Overview").Cells(Startrow + c - 1,
Column2).Value, 1) = "A" Then
* * * * * * If Left(Worksheets("BLCK Overview").Cells(Startrow + c - 1,
Column2).Value, 8) = lowest Then
* * * * * * * * lowest = lowest + "A"
* * * * * * End If
* * * * End If
* * Next c

* * If Worksheets("BLCK Overview").Cells(LastSRow, LastSColumn).Value = ""
Then
* * * * NextSmallestBK = ""
* * * * Else
* * * * If lowest < "ZZzzzzzz" Then
* * * * * * NextSmallestBK = lowest
* * * * * * Else
* * * * * * NextSmallestBK = ""
* * * * End If
* * End If

End Function

Function BKCLExist(MasterLotNo, CLLotNo)

* * MasterColumn = Range(MasterLotNo.Address).Column
* * MasterRow = Range(MasterLotNo.Address).Row
* * CLRow = Range(CLLotNo.Address).Row
* * CLColumn = Range(CLLotNo.Address).Column
* * BKCLExist = ""

* * For n = 1 To 118
* * * * If Worksheets("BLCK Overview").Cells(CLRow + n - 1, CLColumn).Value
= Worksheets("BLCK Overview").Cells(MasterRow, MasterColumn).Value Then
* * * * * * BKCLExist = Worksheets("BLCK Overview").Cells(MasterRow,
MasterColumn).Value
* * * * End If

* * Next n

End Function

Function BKKNExist(MasterLotNo, KNLotNo)

* * MasterColumn = Range(MasterLotNo.Address).Column
* * MasterRow = Range(MasterLotNo.Address).Row
* * KNLotRow = Range(KNLotNo.Address).Row
* * KNLotColumn = Range(KNLotNo.Address).Column

* * BKKNExist = False

* * For n = 1 To 118
* * * * If Worksheets("BLCK Overview").Cells(KNLotRow + n - 1,
KNLotColumn).Value = Left(Worksheets("BLCK Overview").Cells(MasterRow,
MasterColumn).Value, 8) Then
* * * * * * BKKNExist = True
* * * * End If

* * Next n

End Function



"Charles Williams" wrote:
I suggest you post the code for a typical function.

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

"Richard Wood" wrote in message
...
I have written a number of custom functions for the manipulation and
sorting
of a large amount of data. These work exactly as I intended and I can not
see
any problem with the way I have coded them, however I am not very
experienced
with VBA or any other programming language for that matter. These
functions
use a range of data from the sheet, and are used on 116 rows of the sheet.

I am not sure if I have placed the code for them in the correct place (in
Module1) them or defined them correctly (Function [function
name](parameter)), as the cause some strange results when other workbooks
are
open at the same time.

When certain other workbooks are open (and active) and a recalculation is
performed, when I return to my original workbook/sheet the custom
functions
have thrown up a "Value!" error (or it might be a REF! error I can't
remember
which). This is not so much of a problem as a simple recalc (F9) corrects
this.

The big problem occurs when I have two versions of my workbook open. (The
workbook is used to hold manufacturing data and a new one is created every
month from a mast file for that months production). When 2 copies of the
workbook are open any change of data in a single cell results in a
recalculation that can take up to 210 minutes to perform.

It is as if the custom functions can be triggered by any recalculation in
any sheet. So when another workbook is open the custom calculation is
triggered and looks at the active sheet for data. It does not find it so
hence the error. When tow of the same sheets are open the custom function
is
triggered in the non active sheet as well as the active one, updates for
the
active one causing the rest of the sheet to recalculate, and a loop of
recalculation results until a point 10 minutes later or so when it
completes.
(this is only supposition on my part as I can not monitor or prove that
this
is what is happening) Needles to say a recalculation that takes less than
a
couple of seconds with only one copy of the workbook open takes minutes
when
two copies are open, which makes me think that somehow the two open
workbooks
are interacting somehow.

The problem is that I need to create a similar workbook (Which will have
to
be open at the same time as the current one) to cover another product we
manufacture, and will have to use both similar functions and additional
slightly more complex ones to achieve the desired result, however if it
takes
as long to recalculate for every cell of data entered the new workbook
will
be unusable.

Thanks in anticipation for any help or direction anyone can give on this.- Hide quoted text -

- Show quoted text -