Problems with Custom Functions and Muiltiple open workbooks

R

Richard Wood

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

Charles Williams

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 said:
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.
Click to expand...
 
R

Richard Wood

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
A

Aviashn

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.

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 said:
I suggest you post the code for a typical function.
Click to expand...
Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm
Click to expand...
Richard Wood said:
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 recalculationis
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 recalculationin
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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
C

Charles Williams

I agree that one of the problems with your functions is that the functions
dont know which workbook/sheet they are referring to.

Another major problem is that you are referring to ranges that are not in
the argument list of the functions.

This does not work properly because Excel does not know when to recalculate
the functions.

An ugly fix is to add Application.Volatile to each function.

A better solution is to make sure that all the ranges being used inside the
function are passed as arguments.

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

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.

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 said:
I suggest you post the code for a typical function.
Click to expand...
Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm
Click to expand...
Richard Wood said:
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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
R

Richard Wood

Thanks for the response. I see what you mean about dining the workbooks and
sheets. The shht definition is easy as they are static, regardless of the
workbook name. however the workbook name will change everytime I create the
monthly copy from the master.

I know this is lazy, but to save me having to spend time finding out how get
the name of the workbook to use in the definition, any chance you could
supply me with the code?

VBA for excel is nothing like basic on the ZX81 (where my programming
education started and stopped)

Thanks

Aviashn said:
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.

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 said:
I suggest you post the code for a typical function.
Click to expand...
Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
Click to expand...
Click to expand...
 
R

Richard Wood

Charles,

Thanks for the reponse. Will the ugly fix (application.volitile) work as
well as passing the ranges as arguments? I am just after a quick fix for now,
and the change to the argument will take some time to sort out, due to having
also to rewrite the formulas in the sheet to supply the ranges. I will
cretainly try it, as a learning opertunity for myself, but if the
application.volitile fix works, at least I can get the second workbook done
(passing all the ranges as arguments) and running before going back and
recoding the current one.

Regards
Richard

Charles Williams said:
I agree that one of the problems with your functions is that the functions
dont know which workbook/sheet they are referring to.

Another major problem is that you are referring to ranges that are not in
the argument list of the functions.

This does not work properly because Excel does not know when to recalculate
the functions.

An ugly fix is to add Application.Volatile to each function.

A better solution is to make sure that all the ranges being used inside the
function are passed as arguments.

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

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.

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 said:
I suggest you post the code for a typical function.
Click to expand...
Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
C

Charles Williams

Inside a VBA UDF Application.Caller gives you the range that called the UDF.

Then you can use Application.Caller.Parent to get the worksheet that
contains the calling formula,
and Application.Caller.Parent.Parent to get the workbook that contains the
worksheet.

If the range is passed in as a parameter then you can find its parents in
the same way, although you don't usually need to do this with ranges passed
in to the UDF because they are fully qualified (Excel/VBA always knows
exactly where they are) anyway.

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

Richard Wood said:
Thanks for the response. I see what you mean about dining the workbooks
and
sheets. The shht definition is easy as they are static, regardless of the
workbook name. however the workbook name will change everytime I create
the
monthly copy from the master.

I know this is lazy, but to save me having to spend time finding out how
get
the name of the workbook to use in the definition, any chance you could
supply me with the code?

VBA for excel is nothing like basic on the ZX81 (where my programming
education started and stopped)

Thanks

Aviashn said:
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.

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



:
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

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.
Click to expand...
Click to expand...
Click to expand...
 
C

chadwaraksa

I know this is lazy, but to save me having to spend time finding out how get
the name of the workbook to use in the definition, any chance you
could
supply me with the code?

The easiest way is probably to use the ThisWorkbook object.
Dim wksBLCK_Classified As Worksheet
Set wksBLCK_Classified = ThisWorkbook.Worksheets("BLCK
Classified")

Alternately, you can get the name using the ".name" property
debug.print ActiveWorkbook.name

or just
Dim MyWorkbook as Workbook
set MyWorkbook = ActiveWorkbook

Good luck
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Custom Function Problems when multiple workbooks are open 1
Functions not firing! 2
Custom Document Properties in Word documents and Excel workbooks 13
Unwanted workbooks on startup 2
How to open workbooks in different Excel? 2
MACRO TO COUNT ALL OPEN WORKBOOKS and print 1
COM addin won't connect when workbook opened via another workbookfrom another instance 3
Multiple Workbooks Using the Same Custom Toolbar 2

Top