Calling Add-in functions from VBA

[Mark]

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

 

[Ronald Dodge]

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

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

 

[Mark]

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
When as I said in my first post
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

 

[Ronald Dodge]

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

 

[Mark]

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

 

[Ronald Dodge]

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)