• VBA Newbie Asks A Broad Question

    • This topic has 8 replies, 8 voices, and was last updated 15 years ago.
    Author
    Topic
    #468313

    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.

    Viewing 6 reply threads
    Author
    Replies
    • #1219541

      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”).

    • #1219722

      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)

    • #1220272

      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 http://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.

      • #1220301

        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.

    • #1220333

      Thanks, Aubrey, for sharing this specialized info here.

      Gary

    • #1220335

      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.

    • #1220423

      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.

    • #1220723

      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.

    Viewing 6 reply threads
    Reply To: VBA Newbie Asks A Broad Question

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: