Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Dec 2009
    Location
    Olympia, Washington
    Posts
    52
    Thanks
    6
    Thanked 3 Times in 3 Posts
    I would like to ask experienced VBA programmers if my proposed app will fit under the VBA umbrella, or if I should look elsewhere. I am looking for general "can do | no can do" responses. Of course, I am trying to avoid investing many hours of research into VBA inner workings, only to arrive at "No can do". Being willing to pay for helpful tools, I wrote Windmill Software to see if their offerings might help, but they either no longer exist or are simply not response-able.

    The app requires me to read a rotary encoder on a USB port (It comes with VBA example code). When the encoder says we are at the next data point, I read a digital meter on another USB port, convert reading to engineering units, and pass the encoder and meter data to Excel in real time. Excel then plots these data on a scrolling graph in real time. I would like to know if experienced VBA folks see any fatal "gotchas" in this plan. I would also appreciate any suggestions concerning newbie level tutorials and|or third party tools which would help me to avoid "re-inventing the wheel" in developing this app. Any input on the advisability of going down the VBA/Excel road would be very much appreciated. I have experience in VB (not VBA) and Excel.

    Thanks in advance for your input on this subject.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is certainly do-able in Excel VBA. If you can write VB (I assume classic VB), VBA is a no-brainer. Take head that VBA is a subset of VB6 and hence some things aren't there (especially the forms package is less complete).

    If the frequence of readings isn't too high I see no problem (since you mention "real time").
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    towcester, england, UK
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i completely agree with pieterse, but have a different slant on the solution as below..

    are you just going down the VBA route just because the example you have is in vba? ... in which case are you boning up on vba just for that reason? Since excel objects can be created in VB with next to no fuss why not create your app in VB (using skills you are already comfortable with) and write the necessary data to excel via an excel.application object reference.

    may save yourself a bit of effort as you only need spend time on learning the very minimum of VBA/Excel control that you need to get the job done... (ie in very simplest form - open excel object, write data, close object)

  5. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, Ontario, Canada
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Let me start of with full disclosure: I am the author of a book on the use of Excel for electronic engineers. In the book I describe two different applications of acquiring current data ("real time" to me means speeds far higher than Windows is capable of) and reading into a worksheet and displaying it using Excel's charting capabilities. One used the serial port and the other the parallel port, I have also written a series of articles that use data from the serial port in VB.net to access Excel. I will try to summarize my experiences

    1. the scan times that Windows allows for the different drivers are non-deterministic i.e. you have no idea the time that will elapse from one access to the next. I have not done much work with USB so it could be that it is more predictable (and faster) than the serial port, but beware that if your rotary encoder is moving fast and there is no way of buffering the data (and if the encoder is simply incremental) you can intermittently lose data.

    2. philadams-uk suggests working in VB and then accessing Excel. I found that excel.application object reference is not that well documented and is highly dependent on the version of Excel that you are interfacing with. I am uncertain if the objects that you design for will even be compatible in future releases of Excel, let alone previous releases. There is not much available in the literature on the topic, and what there is is often contradictory possibly because of the different versions of Excel. So it is very much trial and error.

    3. Provided the USB connection "tool" is avaliable in VBA or there is a DLL (or even 3rd party drivers), interfacing within Excel is relatively easy and as pieterse says, if you can get it to work in VB it will be very similar if not identical in VBA. Since you have sample code, this is the approach I would try first provided you can live with the slow response of Windows.

    Interfacing the PC to a USB port in VBA probably has several books, but Jan Axelson covers the topic pretty thoroughly from an electrical engineering perspective in her book "USB complete". She also has written articles on this and similar topics in "Circuit Cellar" and "Nuts and Volts". There is also some data on her web site www.lvr.com It would seem to me that you would need to merge what she says with parts of my writings. My article on accessing Excel from VB.net is called "Generic Modbus Simulator " and appeared in the March and April 2007 issues of Circuit Cellar. My book is called "Excel by Example: A Microsoft Excel Cookbook for Electronics Engineers " and I apologise if this plug violates the rules of the forum.

  6. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by Aubrey Kagan View Post
    2. philadams-uk suggests working in VB and then accessing Excel. I found that excel.application object reference is not that well documented and is highly dependent on the version of Excel that you are interfacing with. I am uncertain if the objects that you design for will even be compatible in future releases of Excel, let alone previous releases. There is not much available in the literature on the topic, and what there is is often contradictory possibly because of the different versions of Excel. So it is very much trial and error.
    There are differences between Excel 2000, 2002, 2003 and 2007, but I suspect there is good backwards compatibility for output functions like populating a sheet with data. The UI, of course, is all new in 2007. Since VBA would encounter these same compatibility issues, I'm not sure it is an argument for avoiding VB. But the VBA editing environment provides a good place to develop Excel-specific routines because there are no explicit compile cycles and the help and IntelliSense are very handy. With a little planning, a VBA solution could be easy to port to VB.

    == Edit ==

    I was thinking of classic VB, and not VB.Net. I have no knowledge of VB.Net.

  7. #6
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 28 Times in 27 Posts
    Thanks, Aubrey, for sharing this specialized info here.

    Gary

  8. #7
    Star Lounger
    Join Date
    Dec 2009
    Location
    Olympia, Washington
    Posts
    52
    Thanks
    6
    Thanked 3 Times in 3 Posts
    Thanks folks for your thoughtful ideas. This app has very low data rates and will run on a laptop in the field. The desire is to generate a quasi-realtime plot to monitor data quality as it is acquired. When the data are fully processed back at the lab, it's a bit late and/or difficult and expensive to perform any re-acquisition. A quality check at acquire time is realistic at today's hardware prices, and I'm trying to avoid writing some big custom ball of code. These data are processed in the lab typically with spreadsheets (Now, several hundred thousand rows are no problem!) Since these templates are already written, moving the spreadsheet from the lab to to the field seemed like the least painful path. I only mentioned encoder code example as demonstrating ease of this aspect. It did not influence choice of path. I was looking for some sort of general sense of the bumps in the road of getting data from the sensors into Excel "real time". It's looking like some DLLs written for this purpose by some Brits calling themselves Windmill might be the least painful path to take. Nonetheless, I thank all of you for taking your time to give me your ideas on this "one off" research application. I'm an old geezer from the days of vacuum tubes getting a real kick out of how this newfangled internet thingy can connect human minds from around the world. Downright StarTrekian! Thanks for your thoughts.

  9. #8
    New Lounger
    Join Date
    Apr 2010
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I've been using VBA and it's userforms to program SQL server database apps for several years now. It's a very powerful tool, although with the advent of Excel 2007's and Excel 2010 took a tiny hit because it is required to use PtrSafe when using Windows API functions, which disallows callbacks. But the power is still basically there.

    The key part of your question is, getting the data from your scanner to Excel in "real-time." "Real-time" has a lot of meanings to it, but I don't really think Excel is meant to receive data "real-time." A hardware device connected via USB or Serial could write data at near "real-time" to a flat text file or a SQL server. But Excel shines in it's capability of analysis. If I were you I would draw that distinction.

    I think you should focus on using whatever the hardware vendor gave you to store your data on the PC, then I would code something in Excel VBA to read or parse that data into Excel for analysis and graphing.

  10. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,873
    Thanks
    0
    Thanked 79 Times in 75 Posts
    Unless I'm going mad, PtrSafe is only required if you are using 64bit Office 2010 and the Windows API. Versions of VBA prior to VBA7 don't actually have a PtrSafe function.
    Regards,
    Rory
    Microsoft MVP - Excel.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •