Fun (not!) with Excel VBA

[Robert Keith]

Hi All,

I have written a DLL to be called by Excel or Labview to read data from sensors and return them to the spreadsheet or app. The problem is that it works everywhere reliably except in Excel. The first function sets everything up, opens communications (local COMM or Winsock), and gets the device details. Here is the code:

FUNCTION thStartSession ALIAS "thStartSession"(BYVAL thPort AS LONG, BYVAL thIPaddr AS STRING) EXPORT AS STRING
LOCAL i AS LONG
LOCAL rStr, IPstr, fStr AS STRING
    REDIM CRC8tbl(255)
    REDIM CRC16tbl(1,255)
    REDIM rBytes(1)
    REDIM IBLlist(0)
    popCRC
    IF nComm = 0 THEN nComm = FREEFILE
    IBLcount = 0
    ipstr = thipaddr
 
    ipstr = TRIM$(ipstr, ANY CHR$(0))  'not sure if this is required
 
    IF LEN(ipstr) > 0 THEN
        UseSoc = 1
        i = StartSoc(thport,ipstr)
    ELSE
        UseSoc = 0
        i = StartComms(thPort)
    END IF
    IF i = %TRUE THEN       'if the port is correct and the device is found
        rStr = getlinkserno    'get the serial number and add it to the type
        IF LEN(rstr) > 0 THEN
            LinkType = LinkType  & " - " & rstr
        END IF
    ELSE
        LinkType = "Port or Link not found"
    END IF
 
    MSGBOX linktype   'this is here for troubleshooting
                             'it gets this far before crashing Excel
    FUNCTION = linktype
 
END FUNCTION

Subsequent functions read individual sensors and process the data. Everything works fine when called by VB6 or PowerBasic apps. It also works when called from the immediate window in the Excel VBA API. When called from VBA code in Excel it crashes Excel.

All of the other functions, including functions that return strings, work fine in Excel.

I have tried this function in various forms, returning a long or as a SUB leaving the string data to be collected by another finction. All of them get to the last step (the message box) and then crash the same way.

Does anyone have any ideas about what could be going on here?

Thanks in advance,

Rob

[Michael Mattias]

> FUNCTION = linktype

Looks like the string variable 'linktype' goes out of scope. If not otherwise stated it's LOCAL meaning it is deallocated when the procedure ends.

Try making it STATIC , resetting it at entry and see what happens.

Also you might want to look at #DIM ALL.

[John Petty]

Excel decleration not shown, nor is version, but anyway it is a long time since I have written a DLL for Excel but as I remember it only has two basic variable types, STRING and NUMBER. NUMBER is always a Single or Double (forget which), so have you checked what is being received as the port number.

[Knuth Konrad]

That must really be a long time, because since at least Office 2000 and VB 5, the underlaying VBA is exactly the same, meaning Excel (Word, Access) shares all data types with VB.

I guess Michael is on the right track. There are a couple of varibales in that function which are not declared within the function. So they're either globals or not declared at all (#DIM ALL is your friend), whereas all those arrays are REDIMed but not used in that function.

[Edwin Knoppert]

> FUNCTION = linktype

Looks like the string variable 'linktype' goes out of scope. If not otherwise stated it's LOCAL meaning it is deallocated when the procedure ends.

Try making it STATIC , resetting it at entry and see what happens.

Also you might want to look at #DIM ALL.

No!
Function holds the data just fine.
never use statics if you can avoid them.

[Edwin Knoppert]

Excel decleration not shown, nor is version, but anyway it is a long time since I have written a DLL for Excel but as I remember it only has two basic variable types, STRING and NUMBER. NUMBER is always a Single or Double (forget which), so have you checked what is being received as the port number.

VBA?? provides all kinds of vartypes, the VB declaration does it all.
We are not discussing xll addin's right?

[Michael Mattias]

No!
Function holds the data just fine.
never use statics if you can avoid them.

I guess that makes for two (2) opinions.

However...
1. LOCAL string type variables ARE deallocated when the variable goes out of scope, which occurs when the procedure is exited.
2. As wonky and anal as I can get sometimes , "Don't use STATIC variables if you can avoid them " is IMHO plumb silly advice.

[John Petty]

Just a suggestion to look at, The complete list for Excel 2003 is Strings, Numbers, Boolean and Arrays of them. Yes VBA should be able to handle all types and Macro's appear to be written in VBA, just not sure what happens if you declare a variable as LONG in a macro. The OP quotes the DLL works with VB so it was the only difference I could see as it could cause stack size problems and so invalid memory addressing.

[Michael Mattias]

After further review... I think you may be right Edwin... but I think I am, too.

Best guess? When a function is to return a STRING, a new string is created FOR THAT PURPOSE... and if an assigment like FUNCTION=localVar is used to make that assignment, localvar's contents are copied to that new string. LocalVar is still de-allocated, but it doesn't matter, because that is not what is returned to the caller.

I'm stickin' by my story re STATIC variables, however.

[John Petty]

> FUNCTION = linktype

Looks like the string variable 'linktype' goes out of scope. If not otherwise stated it's LOCAL meaning it is deallocated when the procedure ends.

Try making it STATIC , resetting it at entry and see what happens.

Also you might want to look at #DIM ALL.

His function uses linktype not linktype$ so it must be declared as a global or it would not compile. The OP stated he has tried leaving the string to be recovered by another function so that would make sense. I can't see anything to indicate he is not using Dim All, could you be more specific. In any case how can it go out of scope inside a single function.

[Knuth Konrad]

I can't see anything to indicate he is not using Dim All,

It's more a question, as there are some vars used in that function, which are not declared there. So either these are global vars are #DIM ALL is not used.

Either way, the code shown is not enough to figure out what's going on. Other procedures ware called from there and the calling Excel code (and working VB code) is also missing.

Given what we do know, it's just wild guessing we're all doing here.

[John Petty]

It's more a question, as there are some vars used in that function, which are not declared there. So either these are global vars are #DIM ALL is not used.

Can't agree, so must defend a fellow antipodean. As there is not a single variable in that function suffixed with $,!,$,% or # then the compiler would complain unless they were properly declared elsewhere

[Edwin Knoppert]

>"Don't use STATIC variables if you can avoid them " is IMHO plumb silly advice.

Suggesting static's (as globals) is bad advice since these are not threadsafe.

(#1)
There are scenario's statics are needed but they should not be promoted without understanding, in this case you didn't understand the stack principle for returning values.
That's not a plus for you today.

2nd: "plumb silly advice" is not nice with respect to #1

[Michael Mattias]

>2nd: "plumb silly advice" is not nice with respect to #1

Well, I DID make it "IMHO" which is the one against which I will accept arguments, as opposed to "IMNSHO" which is cast in stone as far as I'm converned.

[Robert Keith]

Thanks all

Thank you for all of the input.

LinkType is a Global dynamic string. It is used elsewhere for license validation. I use #DIM ALL. I am still shaking off the ingrained habits of 10 years of using VB so the type identifiers are still foreign.

I am using Excel 2003 which includes VBA6, not macros or add-ins. I would like to make it compatible with Excel 97 which includes VBA5.

I thought of making the returned data in VB a Variant or Anything but the function still crashes if I use Call or change it to a Sub.

I will write a test program in VB 2008. It gives much better messages about memory issues.

Cheers,

Rob

[Knuth Konrad]

LinkType is a Global dynamic string. It is used elsewhere for license validation. I use #DIM ALL. I am still shaking off the ingrained habits of 10 years of using VB so the type identifiers are still foreign.

You don't need to use type identifiers. I seldom run across code these days which includes them.

But that still suggests you're using a lot of global variables, which was/is a bad habit in VB, too.

[John Petty]

Actually it shouldn't work with VBA6 either for the simple reason you can't pass a string from VBA to PB BYVAL ie the IPaddress. If it is declared as BYVAL in VBA then it will be received as BYREF ASCIIZ by PB.
Look up Strings under Visual Basic in the PB help.

[Robert Keith]

More clues

Thanks again. That has given me some more ideas.

The dll now works fine in a VB6 application (dodgy stings and all) but Excel still goes up in flames. We are trying it out with LabView which is the real purpose of it.

Some more clues are:

* Sometimes Excel gets a (string) value back a few seconds before freezing and crashing.

* Excel requires the dll to be in the system32 folder and the VB6 application can access it in from the same folder.

* The VB app releases the dll when it has stopped running the functions. Excel needs to be closed completely before the dll can be overwritten with a new version.

Cheers,

Rob