Announcement

Collapse
No announcement yet.

Fun (not!) with Excel VBA

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Robert Keith
    replied
    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

    Leave a comment:


  • John Petty
    replied
    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.

    Leave a comment:


  • Knuth Konrad
    replied
    Originally posted by Robert Keith View Post
    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.

    Leave a comment:


  • Robert Keith
    replied
    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

    Leave a comment:


  • Michael Mattias
    replied
    >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.

    Leave a comment:


  • Edwin Knoppert
    replied
    >"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

    Leave a comment:


  • John Petty
    replied
    Originally posted by Knuth Konrad View Post
    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

    Leave a comment:


  • Knuth Konrad
    replied
    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.

    Leave a comment:


  • John Petty
    replied
    Originally posted by Michael Mattias View Post
    > 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.

    Leave a comment:


  • Michael Mattias
    replied
    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.

    Leave a comment:


  • John Petty
    replied
    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.

    Leave a comment:


  • Michael Mattias
    replied
    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.

    Leave a comment:


  • Edwin Knoppert
    replied
    Originally posted by John Petty View Post
    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?

    Leave a comment:


  • Edwin Knoppert
    replied
    Originally posted by Michael Mattias View Post
    > 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.

    Leave a comment:


  • Knuth Konrad
    replied
    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.

    Leave a comment:


  • John Petty
    replied
    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.

    Leave a comment:


  • Michael Mattias
    replied
    > 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.

    Leave a comment:


  • Robert Keith
    started a topic Fun (not!) with Excel VBA

    Fun (not!) with Excel VBA

    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:

    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
Working...
X