en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en nl en en en ru en en en en en en en en en de en en en en en nl en en pl en en en en en fr en da en en en en en en en nl en en en en en en en en en en nl en en en en en en en en en en en nl en en en pl en en en it en en en en en en en en en en nl en en en en en en en en en en en en en en nl vi en it en en en en en en en en fr en da en en en en en en en nl en en en en en en en en en en nl fr da en en en en en fr en da en en en en en en en nl en en en pl en en en en en en en en en en en nl en en en en en en en en en en en en en nl en en en en en en en en en en en en nl en en en en ja en en en en en en en en en en en nl en en en en en en fr en da en en en en en en en nl en en en en en en en en en en en en en en en en en nl en en en en en en en en en en en en en nl en cs en en en en en en en en en nl en en en en en en en en en en en en en en en de en en en fr en en en en en en en en en pt en en en en en en
By logging into your account, you agree to our Privacy Policy, personal data processing and storage practices as described therein.
Forgot password or user name?
Search in titles only Search in Programming MS Office only
You are not logged in. You can browse in the PowerBASIC Community, but you must click Login (top right) before you can post. If this is your first visit, check out the FAQ or Sign Up.
Fun (not!) with Excel VBA
All Time Today Last Week Last Month
All Discussions only Photos only Videos only Links only Polls only Events only
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:
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?
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.
2 Sep 2009, 09:16 AM
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.
Join Date: Apr 2000
2 Sep 2009, 10:10 AM
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.
Join Date: Jun 2000
2 Sep 2009, 10:29 AM
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.
Function holds the data just fine.
never use statics if you can avoid them.
Join Date: Jun 2000
2 Sep 2009, 10:30 AM
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?
Join Date: Aug 1998
2 Sep 2009, 10:49 AM
Function holds the data just fine.
never use statics if you can avoid them.
I guess that makes for two (2) opinions.
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.
Port Washington WI USA
Join Date: Feb 2000
2 Sep 2009, 10:49 AM
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.
Join Date: Aug 1998
2 Sep 2009, 10:57 AM
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.
Port Washington WI USA
Join Date: Feb 2000
2 Sep 2009, 10:59 AM
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.
Join Date: Apr 2000
2 Sep 2009, 11:54 AM
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.
Join Date: Feb 2000
2 Sep 2009, 01:29 PM
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
Join Date: Jun 2000
2 Sep 2009, 01:41 PM
>"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.
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
Join Date: Aug 1998
2 Sep 2009, 01:45 PM
>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.
Port Washington WI USA
Join Date: Feb 2009
2 Sep 2009, 06:06 PM
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.
Join Date: Apr 2000
3 Sep 2009, 04:25 AM
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.
Join Date: Feb 2000
3 Sep 2009, 10:13 AM
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.
Join Date: Feb 2009
3 Sep 2009, 05:10 PM
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.
Copyright © 1998-2022 PowerBASIC Tools, LLC - All Rights Reserved.
Powered by vBulletin® Version 5.7.0
Copyright © 2022 vBulletin Solutions, Inc. All rights reserved.
All times are GMT-5. This page was generated at 10:27 PM.
We process personal data about users of our site, through the use of cookies and other technologies, to deliver our services, and to analyze site activity. For additional details, refer to our Privacy Policy.
By clicking "I AGREE" below, you agree to our Privacy Policy and our personal data processing and cookie practices as described therein. You also acknowledge that this forum may be hosted outside your country and you consent to the collection, storage, and processing of your data in the country where this forum is hosted.