Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Add-in functions from VBA
Experts,
I have one specific and one general question about add-ins. System: NT4/XL97 Files: Swift accord matrix.xls gbcmcrolib.xla otcmcrolib.xla I use an add-in (otcmcrolib.xla) to highlight rows in a worksheet which meet certain criteria. The criteria are stored in an XLS file (Swift accord matrix.xls). This XLS file has the following code in the Workbook_BeforeClose event: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim rngLastCell As Range Dim iRowNumber As Integer Dim wksMessageTimings As Worksheet Set wksMessageTimings = Workbooks("Swift accord matrix.xls").Worksheets("Sheet1") 'Locate the last cell in the sheet. Set rngLastCell = rngOp01_IdentifyLastCell(wksMessageTimings) 'Get the row number from this. iRowNumber = rngLastCell.Row 'Add the range as a name. ThisWorkbook.Names.Add Name:="MessageTimings", _ RefersToR1C1:=Range(Cells(3, 4), Cells(iRowNumber, 8)) End Sub rngOp01_IdentifyLastCell is an add-in function which accepts a worksheet object and returns a range object (the add-in gbcmcrolib.xla is included in the references of the project). otcmcrolib.xla contains the following code in a code module: Dim wbk as Workbook Set wbk = Workbooks.Open("[path]\Swift accord matrix.xls") Code which uses one of the names in Swift accord matrix.xls in a VLOOKUP wbk.Close When executing the line of code Set rngLastCell = rngOp01_IdentifyLastCell(wksMessageTimings) in the Workbook_BeforeClose() event above I get the following error: Run time error '91'; Object variable or with block variable not set When I step through the code, it seems like the Worksheet gets passed to rngOp01_IdentifyLastCell (ie ws.Application.ActiveCell.Value is the value in Sheet1!A1 of Swift accord matrix.xls) only the code Public Function rngOp01_IdentifyLastCell(ws As Worksheet) Dim lLastRow As Long Dim iLastCol As Integer 'Error-handling in case no data in the worksheet. _ Needs to be enhanced. On Error Resume Next With ws 'Find the last real row lLastRow = .Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByRows).Row 'Find the last real column iLastCol = .Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With 'Return statement Set rngOp01_IdentifyLastCell = ws.Cells(lLastRow, iLastCol) End Function can't operate on it. All this code works fine when I open and close the XLS file directly. It is just when it is opened and closed via the add-in (otcmcrolib.xla) in the manner described above that it doesn't work. Can anyone give me any insight? General add-in question: Is there a good source of information about add-ins which goes beyond the introductory 'all you have to do is SaveAs .xla and your Workbook becomes an add-in and users can't see your data and won't be prompted for saving' etc etc because I have come across more than a few infuriating little things like my problem described above and presume there is a great deal I am missing on the subject? Thanks everyone, Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Add-in functions from VBA
I didn't notice anything in particular other than that you didn't declare
your function as a range object, which is a good idea to not only declare your variables fully (if feasible), but also your functions. The RefersTo part of your Names.Add Method needs to be in String setting, not in a Range Object setting., of which you can use the Address Property after the Range Object such as adding the following to your range object: ..Address(ReferenceStyle:=xlR1C1) One other thing that I had done early on as I was learning with the macro recorder, but weeded out later due to issues that I ran into (note, this was not a bug of Excel), the code used a lot of active objects in it's recording, which worked for a while, but as I got deeper into the code and started working with the code, some of those active objects were starting to cause issues, so I went back, gone through my code and changed what all I could from the active objects to either actual objects (static) or to variables which referenced the objects (dynamic). To get a better understanding of XLAs, you may want to look at the VBA help file dealing with the AddIn Object. That serves as a good starting point from the programming side of things. I no longer use XL97 as all of my users has been switched to XLXP within the last 2 weeks, but I suspect that the help file is in it. The only thing that I really like about XL97 over the later versions is the fact that the help files gives more information. However, I didn't like the bugginess of XL97 that I had to contend with that XL2K was greatly improved on. -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Mark" wrote in message om... Experts, I have one specific and one general question about add-ins. System: NT4/XL97 Files: Swift accord matrix.xls gbcmcrolib.xla otcmcrolib.xla I use an add-in (otcmcrolib.xla) to highlight rows in a worksheet which meet certain criteria. The criteria are stored in an XLS file (Swift accord matrix.xls). This XLS file has the following code in the Workbook_BeforeClose event: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim rngLastCell As Range Dim iRowNumber As Integer Dim wksMessageTimings As Worksheet Set wksMessageTimings = Workbooks("Swift accord matrix.xls").Worksheets("Sheet1") 'Locate the last cell in the sheet. Set rngLastCell = rngOp01_IdentifyLastCell(wksMessageTimings) 'Get the row number from this. iRowNumber = rngLastCell.Row 'Add the range as a name. ThisWorkbook.Names.Add Name:="MessageTimings", _ RefersToR1C1:=Range(Cells(3, 4), Cells(iRowNumber, 8)) End Sub rngOp01_IdentifyLastCell is an add-in function which accepts a worksheet object and returns a range object (the add-in gbcmcrolib.xla is included in the references of the project). otcmcrolib.xla contains the following code in a code module: Dim wbk as Workbook Set wbk = Workbooks.Open("[path]\Swift accord matrix.xls") Code which uses one of the names in Swift accord matrix.xls in a VLOOKUP wbk.Close When executing the line of code Set rngLastCell = rngOp01_IdentifyLastCell(wksMessageTimings) in the Workbook_BeforeClose() event above I get the following error: Run time error '91'; Object variable or with block variable not set When I step through the code, it seems like the Worksheet gets passed to rngOp01_IdentifyLastCell (ie ws.Application.ActiveCell.Value is the value in Sheet1!A1 of Swift accord matrix.xls) only the code Public Function rngOp01_IdentifyLastCell(ws As Worksheet) Dim lLastRow As Long Dim iLastCol As Integer 'Error-handling in case no data in the worksheet. _ Needs to be enhanced. On Error Resume Next With ws 'Find the last real row lLastRow = .Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByRows).Row 'Find the last real column iLastCol = .Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With 'Return statement Set rngOp01_IdentifyLastCell = ws.Cells(lLastRow, iLastCol) End Function can't operate on it. All this code works fine when I open and close the XLS file directly. It is just when it is opened and closed via the add-in (otcmcrolib.xla) in the manner described above that it doesn't work. Can anyone give me any insight? General add-in question: Is there a good source of information about add-ins which goes beyond the introductory 'all you have to do is SaveAs .xla and your Workbook becomes an add-in and users can't see your data and won't be prompted for saving' etc etc because I have come across more than a few infuriating little things like my problem described above and presume there is a great deal I am missing on the subject? Thanks everyone, Mark |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Add-in functions from VBA
Ronald,
Thanks for your response. I have tried what you suggested but still haven't succeeded. Indeed as I have expanded the toolset I have encountered other errors along the same lines. I think I have a fundamental mis-understanding of passing data between projects. I can pass primitive types between projects no problem, it just breaks down (or only works inconsistently) when I try to pass objects. As in the example below: Public Function rngOp01_IdentifyLastCell(ws As Worksheet) As Range Dim lLastRow As Long Dim iLastCol As Integer On Error Resume Next With ws 'Find the last real row lLastRow = .Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByRows).Row 'Find the last real column iLastCol = .Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With 'Return statement Set rngOp01_IdentifyLastCell = ws.Cells(lLastRow, iLastCol) End Function When as I said in my first post When I step through the code, it seems like the Worksheet gets passed to rngOp01_IdentifyLastCell (ie ws.Application.ActiveCell.Value is the value in Sheet1!A1 of Swift accord matrix.xls) only the code can't operate on it (ie iLastCol and lLastRow don't get set). And more recently when I call another function (calFunc01_GetDailyReportDate)which accepts a date and returns a date, the date gets passed and the function works on it but it is not returned. In desparation I have changed this function to have it return an integer which it does no problem so I suppose I could write a date converter but I don't want to. I want my function library (read Add-in) to work. So, my questions: Which if any of these is the format of a function return statement? function_name() = result function_name = result Set function_name() = result Set function_name = result Often when I add a reference to the add-in (gbcmcrolib.xla) to a project I get the "Bad NT Image" message. Could this be the cause of my inability to pass objects? Anything of interest about passing objects between projects? What I'm going to do: I am going to take the code from my project, make another add-in with it, reference it in the same project and see if this can pass objects. Thanks everyone, Mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Add-in functions from VBA
With the question you asked, this is where terminology can get to be rather
sticky to distinguish, but let me give it a shot as to when to use the Set as opposed to when not to, just as there are times when to use the parantheses and when not to. When do you use the Set Statement? This statement is primarily used to set a reference of an actual object to a datatype variable of it's object type. Note, data types and variables can be thought of as objects, but I don't think of them as true objects in this since. In your example, range/cell you are trying to reference to is an actual object, thus the Set statement needs to be used in that case. Example: Set Rng = ws.Cells(5,3) Sets the Rng data variable to refer to Cell "C5" that is in the worksheet that the ws Worksheet datatype references to. When you are only using variables or other expressions that is not an actual object or doesn't reference to an actual object, then you don't use the Set statement. The other 2 variables that you have setup in your example, they don't get the Set command (just as you have rightfully left off) cause they are only getting information from the objects to allow the variable contain whatever is the result of the expression, thus in effect, it's really using the Let statement. If it would help, go into the contents part of your Excel VBA help file, and go into "Visual Basic Language Reference" "Statements" then compare the help file on the "Let Statement" to the help file on the "Set Statement". Now when to use the Parantheses of a function name and when not to. First, when the function name is on the left side (Note, this will only occur within the function itself), it does not get added at the end, the parantheses. when the function name is used on the right side of the equation, that's a depend on how you are using the function at that particular point of the code. If the function is intended to be used as a procedure, thus not in an equation and is not setup to return a result, then you omit the parantheses itself, not the arguments unless they are optional and intended to be left out. When a function name is used and expected to return a result, it must be on the right hand side of an equation (unless used within a criteria/condition/where argument) and it must include the parantheses. Examples: Function used as a procedure modFunction.ValidateData Function used within a condition argument If modFunction.ValidateData Then This ValidateData function is assumed to return either a false or '0' (numeric, not string) value to have the condition returns a false answer else it will return a true answer. function returns a result to anther variable. iAnswer = modFunction.ValidateData For what you are trying to do, this is a sample code that I have created so as you may be able to learn from it. Function fncWshtLstCell(WS As Worksheet) Dim lLastRow As Long, iLastCol As Integer With WS 'Find the last real row lLastRow = .Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ searchOrder:=xlByRows).Row 'Implies Let statement, not an actual object reference 'Find the last real column iLastCol = .Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ searchOrder:=xlByColumns).Column 'Implies Let statement, not an actual object reference End With 'The next line references to an actual object. Set fncWshtLstCell = ThisWorkbook.Worksheets(1).Cells(lLastRow, iLastCol) End Function Sub TestWshtLstCell() Dim WS As Worksheet, Rng As Range Set WS = ThisWorkbook.Worksheets(1) 'Actual Object reference Set Rng = CurUser(WS) 'Actual Object reference End Sub -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Mark" wrote in message om... Ronald, Thanks for your response. I have tried what you suggested but still haven't succeeded. Indeed as I have expanded the toolset I have encountered other errors along the same lines. I think I have a fundamental mis-understanding of passing data between projects. I can pass primitive types between projects no problem, it just breaks down (or only works inconsistently) when I try to pass objects. As in the example below: Public Function rngOp01_IdentifyLastCell(ws As Worksheet) As Range Dim lLastRow As Long Dim iLastCol As Integer On Error Resume Next With ws 'Find the last real row lLastRow = .Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByRows).Row 'Find the last real column iLastCol = .Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column End With 'Return statement Set rngOp01_IdentifyLastCell = ws.Cells(lLastRow, iLastCol) End Function When as I said in my first post When I step through the code, it seems like the Worksheet gets passed to rngOp01_IdentifyLastCell (ie ws.Application.ActiveCell.Value is the value in Sheet1!A1 of Swift accord matrix.xls) only the code can't operate on it (ie iLastCol and lLastRow don't get set). And more recently when I call another function (calFunc01_GetDailyReportDate)which accepts a date and returns a date, the date gets passed and the function works on it but it is not returned. In desparation I have changed this function to have it return an integer which it does no problem so I suppose I could write a date converter but I don't want to. I want my function library (read Add-in) to work. So, my questions: Which if any of these is the format of a function return statement? function_name() = result function_name = result Set function_name() = result Set function_name = result Often when I add a reference to the add-in (gbcmcrolib.xla) to a project I get the "Bad NT Image" message. Could this be the cause of my inability to pass objects? Anything of interest about passing objects between projects? What I'm going to do: I am going to take the code from my project, make another add-in with it, reference it in the same project and see if this can pass objects. Thanks everyone, Mark |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Add-in functions from VBA
Ronald,
Thanks for that info it's very helpful. With regard to my problem passing objects between add-ins, I have rewritten the function in question so that it does not use the Worksheet.Find method and it now works. Ofcourse, it's still an unsatisfactory outcome but I think this is an Excel experience I will use to guide me in the future. Here's the code in case you are interested: Public Function rngOp02_IdentifyLastCell(ws As Worksheet) As Range 'Author: Mark Townsend 'Date: 12-09-2003 'Purpose: To identify the last cell in a range. 'Comment: The range need not be contiguous, _ it is really the intersection of the _ last row and last column in all the _ ranges used in a worksheet. 'Arguments: The worksheet object in question. 'Returns: A range object that is the final row _ and final column. Dim rngLastCell As Range Dim rngFirstCell As Range Dim lLastRow As Long Dim iLastCol As Integer Dim aData As Variant Dim iCounter As Integer 'Initialise variables. Set rngFirstCell = ws.UsedRange lLastRow = rngFirstCell.Rows.Count iLastCol = rngFirstCell.Columns.Count 'Get the real last column Do While iLastCol 0 aData = ws.Range(Cells(1, iLastCol), Cells(lLastRow, iLastCol)).Value For iCounter = 1 To UBound(aData, 1) If (Not (IsEmpty(aData(iCounter, 1)))) Then Exit Do Next iCounter iLastCol = iLastCol - 1 Loop 'Get the real last row. Do While lLastRow 0 aData = ws.Range(Cells(lLastRow, 1), Cells(lLastRow, iLastCol)).Value For iCounter = 1 To UBound(aData, 2) If (Not (IsEmpty(aData(1, iCounter)))) Then Exit Do Next iCounter lLastRow = lLastRow - 1 Loop Set rngLastCell = ws.Range("A1").Offset(lLastRow - 1, _ iLastCol - 1) 'Return statement Set rngOp02_IdentifyLastCell = rngLastCell End Function Thanks again, Mark |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Add-in functions from VBA
The way you have determined the last row and last column may or may not be
accurate. That is cause you are assuming that Row 1 and Column A are part of the used range. If this is the case, then your determination will work fine, but if this is not the case, then it will return a number lower than what you expected, so to really return the last row, put the following in place. Dim lLastRow As Long, lFirstRow as Long Dim iLastCol As Integer, iFirstCol as Integer Set rngFirstCell = ws.UsedRange lFirstRow = rngFirstCell.Row iFirstCol = rngFirstCell.Column lLastRow = FirstRow + rngFirstCell.Rows.Count - 1 iLastCol = FirstCol + rngFirstCell.Columns.Count - 1 One other thing though this may or may not impact you. The way you are refering to Cells within your Range Object, it will refer to the Cells of the active worksheet, so to avoid any potential problems, you may want to prequalify your Cells Object with the ws Worksheet Object like ws.Cells(lLastRow,1) -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Mark" wrote in message om... Ronald, Thanks for that info it's very helpful. With regard to my problem passing objects between add-ins, I have rewritten the function in question so that it does not use the Worksheet.Find method and it now works. Ofcourse, it's still an unsatisfactory outcome but I think this is an Excel experience I will use to guide me in the future. Here's the code in case you are interested: Public Function rngOp02_IdentifyLastCell(ws As Worksheet) As Range 'Author: Mark Townsend 'Date: 12-09-2003 'Purpose: To identify the last cell in a range. 'Comment: The range need not be contiguous, _ it is really the intersection of the _ last row and last column in all the _ ranges used in a worksheet. 'Arguments: The worksheet object in question. 'Returns: A range object that is the final row _ and final column. Dim rngLastCell As Range Dim rngFirstCell As Range Dim lLastRow As Long Dim iLastCol As Integer Dim aData As Variant Dim iCounter As Integer 'Initialise variables. Set rngFirstCell = ws.UsedRange lLastRow = rngFirstCell.Rows.Count iLastCol = rngFirstCell.Columns.Count 'Get the real last column Do While iLastCol 0 aData = ws.Range(Cells(1, iLastCol), Cells(lLastRow, iLastCol)).Value For iCounter = 1 To UBound(aData, 1) If (Not (IsEmpty(aData(iCounter, 1)))) Then Exit Do Next iCounter iLastCol = iLastCol - 1 Loop 'Get the real last row. Do While lLastRow 0 aData = ws.Range(Cells(lLastRow, 1), Cells(lLastRow, iLastCol)).Value For iCounter = 1 To UBound(aData, 2) If (Not (IsEmpty(aData(1, iCounter)))) Then Exit Do Next iCounter lLastRow = lLastRow - 1 Loop Set rngLastCell = ws.Range("A1").Offset(lLastRow - 1, _ iLastCol - 1) 'Return statement Set rngOp02_IdentifyLastCell = rngLastCell End Function Thanks again, Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calling an Add-In | Excel Discussion (Misc queries) | |||
Calling certain functions within VBA | Excel Programming | |||
Calling certain functions within VBA | Excel Programming | |||
Calling certain functions within VBA | Excel Programming | |||
Calling VBA functions from a xll | Excel Programming |