Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Custom Functions and Muiltiple open workbooks
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 cant 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Custom Functions and Muiltiple open workbooks
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Custom Functions and Muiltiple open workbooks
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Custom Functions and Muiltiple open workbooks
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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Custom Functions and Muiltiple open workbooks
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 "Aviashn" wrote in message ... 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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Custom Functions and Muiltiple open workbooks
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" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Custom Functions and Muiltiple open workbooks
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" wrote: 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 "Aviashn" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Custom Functions and Muiltiple open workbooks
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" wrote in message ... 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" wrote: 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with Custom Functions and Muiltiple open workbooks
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Functions Available for all Workbooks | Excel Worksheet Functions | |||
custom menu cross contamination between open workbooks. | Excel Programming | |||
How to access ranges in closed workbooks in custom functions | Excel Programming | |||
Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed | Excel Programming | |||
Need help with custom open/save functions | Excel Programming |