Send and receive information between Excel/VBA and C

Discussion in 'C Programming' started by jari.toukkari@jippii.fi, Dec 22, 2007.

  1. Guest

    Hi

    I use a large txt file of 1,5m lines which VBA reads into memory, and
    then can searches certain values out of that data. Seach could be
    faster and I wonder if c or some other language could be used for the
    search engine.

    So, Excel/VBA would send a variable and a big array to C, which would
    search given variable from the array, and return much smaller array of
    hits back to VBA.

    Is this possible? How? Any kind of help needed

    Regards, Jari
    , Dec 22, 2007
    #1
    1. Advertising

  2. Ivan Novick Guest

    On Dec 22, 10:19 am, wrote:
    > Hi
    >
    > I use a large txt file of 1,5m lines which VBA reads into memory, and
    > then can searches certain values out of that data. Seach could be
    > faster and I wonder if c or some other language could be used for the
    > search engine.
    >
    > So, Excel/VBA would send a variable and a big array to C, which would
    > search given variable from the array, and return much smaller array of
    > hits back to VBA.
    >
    > Regards, Jari


    Before changing languages... how about changing your algorithm? Is it
    possible for the data to be maintained in sorted order or some other
    more clever order so that you don't need to do a linear "search" for
    the hits?

    Ivan Novick
    http://www.0x4849.net
    Ivan Novick, Dec 22, 2007
    #2
    1. Advertising

  3. Guest

    Thanks Ivan for replying.

    I have done a simple index when data is read into memory to make
    searches faster. In this case index is based on manufacturer name,
    which limits searched items to max 150.000, which is fast enough, and
    small enough number of lines for VBA.
    Text file has been sorted using a sort table of around 10 criterias to
    make a fast hit, and find the more suitable items.

    Though quite often manufacturer name is not known and all 1,5M lines
    are searched from. This is where I need something faster and smarter
    than I have today.

    I thought it would be a good idea so send the searched item to a small
    program created with C or any fast language. That would do the dirty
    work and send a result table back to VBA.

    Maybe I've seen wet daydreams again

    Jari


    On Dec 22, 9:53 pm, Ivan Novick <> wrote:
    > On Dec 22, 10:19 am, wrote:
    >
    > > Hi

    >
    > > I use a large txt file of 1,5m lines which VBA reads into memory, and
    > > then can searches certain values out of that data. Seach could be
    > > faster and I wonder if c or some other language could be used for the
    > > search engine.

    >
    > > So, Excel/VBA would send a variable and a big array to C, which would
    > > search given variable from the array, and return much smaller array of
    > > hits back to VBA.

    >
    > > Regards, Jari

    >
    > Before changing languages... how about changing your algorithm? Is it
    > possible for the data to be maintained in sorted order or some other
    > more clever order so that you don't need to do a linear "search" for
    > the hits?
    >
    > Ivan Novickhttp://www.0x4849.net
    , Dec 23, 2007
    #3
  4. CBFalconer Guest

    wrote: ** and top-posted. Fixed **
    > Ivan Novick <> wrote:
    >> wrote:
    >>
    >>> I use a large txt file of 1,5m lines which VBA reads into memory,
    >>> and then can searches certain values out of that data. Seach
    >>> could be faster and I wonder if c or some other language could be
    >>> used for the search engine.

    >>
    >>> So, Excel/VBA would send a variable and a big array to C, which
    >>> would search given variable from the array, and return much
    >>> smaller array of hits back to VBA.

    >>
    >> Before changing languages... how about changing your algorithm?
    >> Is it possible for the data to be maintained in sorted order or
    >> some other more clever order so that you don't need to do a linear
    >> "search" for the hits?

    >
    > I have done a simple index when data is read into memory to make
    > searches faster. In this case index is based on manufacturer name,
    > which limits searched items to max 150.000, which is fast enough,
    > and small enough number of lines for VBA. Text file has been
    > sorted using a sort table of around 10 criterias to make a fast
    > hit, and find the more suitable items.
    >
    > Though quite often manufacturer name is not known and all 1,5M
    > lines are searched from. This is where I need something faster
    > and smarter than I have today.


    I suggest you take a look at hash tables. I have published a
    library for general use (GPL licensed, but other arrangements can
    be made with me), written in portable standard C, which has various
    features for portability. You can find it at:

    <http://cbfalconer.home.att.net/download/>

    which may (or may not) be suitable for your application.

    Please do not top-post. Your answer belongs after (or intermixed
    with) the quoted material to which you reply, after snipping all
    irrelevant material. I fixed this one. See the following links:

    --
    <http://www.catb.org/~esr/faqs/smart-questions.html>
    <http://www.caliburn.nl/topposting.html>
    <http://www.netmeister.org/news/learn2quote.html>
    <http://cfaj.freeshell.org/google/> (taming google)
    <http://members.fortunecity.com/nnqweb/> (newusers)



    --
    Posted via a free Usenet account from http://www.teranews.com
    CBFalconer, Dec 24, 2007
    #4
  5. Ivan Novick Guest

    On Dec 23, 5:03 pm, CBFalconer <> wrote:
    > I suggest you take a look at hash tables. I have published a
    > library for general use (GPL licensed, but other arrangements can
    > be made with me), written in portable standard C, which has various
    > features for portability. You can find it at:
    >
    > <http://cbfalconer.home.att.net/download/>
    >

    If you are going to go this route why not use Apache Portable Runtime
    library?

    http://apr.apache.org/docs/apr/1.2/group__apr__hash.html

    Ivan Novick
    http://www.0x4849.net
    Ivan Novick, Dec 24, 2007
    #5
  6. CBFalconer Guest

    Ivan Novick wrote:
    > CBFalconer <> wrote:
    >
    >> I suggest you take a look at hash tables. I have published a
    >> library for general use (GPL licensed, but other arrangements can
    >> be made with me), written in portable standard C, which has various
    >> features for portability. You can find it at:
    >>
    >> <http://cbfalconer.home.att.net/download/>

    >
    > If you are going to go this route why not use Apache Portable
    > Runtime library?
    >
    > http://apr.apache.org/docs/apr/1.2/group__apr__hash.html


    I took a cursory look at your reference. I think hashlib is much
    more flexible, and simpler to understand and use. I KNOW it uses
    only pure ISO standard code, suitable for both C90 and C99
    compilers. Here is a short excerpt from the documentation
    (hshusage.txt) file:

    Using hashlib
    =============

    First, we need some way to refer to the table. So we must
    have a data item of type hshtbl* to hold it. We will initialize
    that by calling hshinit. This is much like opening a file. For
    convenience here is the prototype for hshinit again:

    /* initialize and return a pointer to the data base */
    hshtbl *hshinit(hshfn hash, hshfn rehash,
    hshcmpfn cmp,
    hshdupfn dupe, hshfreefn undupe,
    int hdebug);

    Now this following is a fragment from your code:

    hshtbl *mytable;

    /* initialize and return a pointer to the data base */
    mytable = hshinit(myhash, myrehash,
    mycmp,
    mydupe, myundupe,
    0);

    which tells hashlib all about the customizing functions you have
    created. Note that all those functions can be static, unless
    you have other uses for them outside your source file. You can
    use those functions yourself as you please.

    Don't forget the final 0 in the call to hshinit. That parameter
    provides for future extensions and debugging abilities, and
    passing a zero here will maintain compatibility.

    You can create more than one hash table if you desire. If they
    handle the same data format you can just do exactly the same
    call as above, except you will need a new variable of type
    hshtbl* to hold the table identification. If they don't hold
    the same data type you can supply different functions to
    hshinit. It is up to you.

    *** end of excerpt ***

    --
    Merry Christmas, Happy Hanukah, Happy New Year
    Joyeux Noel, Bonne Annee, Frohe Weihnachten
    Chuck F (cbfalconer at maineline dot net)
    <http://cbfalconer.home.att.net>



    --
    Posted via a free Usenet account from http://www.teranews.com
    CBFalconer, Dec 25, 2007
    #6
  7. Ivan Novick Guest

    On Dec 24, 5:07 pm, CBFalconer <> wrote:
    > Ivan Novick wrote:
    > > CBFalconer <> wrote:

    >
    > >> I suggest you take a look at hash tables. I have published a
    > >> library for general use (GPL licensed, but other arrangements can
    > >> be made with me), written in portable standard C, which has various
    > >> features for portability. You can find it at:

    >
    > >> <http://cbfalconer.home.att.net/download/>

    >
    > > If you are going to go this route why not use Apache Portable
    > > Runtime library?

    >
    > >http://apr.apache.org/docs/apr/1.2/group__apr__hash.html

    >
    > I took a cursory look at your reference. I think hashlib is much
    > more flexible, and simpler to understand and use. I KNOW it uses
    > only pure ISO standard code, suitable for both C90 and C99
    > compilers. Here is a short excerpt from the documentation
    > (hshusage.txt) file:


    The advantage to using APR or some other widely used library, is it
    has been peer reviewed and tested in production by many people all
    over the world.

    Ivan Novick
    http://www.0x4849.net
    Ivan Novick, Dec 25, 2007
    #7
  8. CBFalconer Guest

    Ivan Novick wrote:
    > CBFalconer <> wrote:
    >> Ivan Novick wrote:
    >>> CBFalconer <> wrote:

    >>
    >>>> I suggest you take a look at hash tables. I have published a
    >>>> library for general use (GPL licensed, but other arrangements
    >>>> can be made with me), written in portable standard C, which has
    >>>> various features for portability. You can find it at:

    >>
    >>>> <http://cbfalconer.home.att.net/download/>

    >>
    >>> If you are going to go this route why not use Apache Portable
    >>> Runtime library?

    >>
    >>> http://apr.apache.org/docs/apr/1.2/group__apr__hash.html

    >>
    >> I took a cursory look at your reference. I think hashlib is much
    >> more flexible, and simpler to understand and use. I KNOW it uses
    >> only pure ISO standard code, suitable for both C90 and C99
    >> compilers. Here is a short excerpt from the documentation
    >> (hshusage.txt) file:

    >
    > The advantage to using APR or some other widely used library, is
    > it has been peer reviewed and tested in production by many people
    > all over the world.


    So has hashlib. I made a trivial adjustment last year. Before
    that the last bug report was in 2002. I have had no further bug
    reports. You obviously haven't looked at it.

    --
    Merry Christmas, Happy Hanukah, Happy New Year
    Joyeux Noel, Bonne Annee, Frohe Weihnachten
    Chuck F (cbfalconer at maineline dot net)
    <http://cbfalconer.home.att.net>



    --
    Posted via a free Usenet account from http://www.teranews.com
    CBFalconer, Dec 25, 2007
    #8
  9. Ivan Novick Guest

    On Dec 25, 11:28 am, CBFalconer <> wrote:
    > Ivan Novick wrote:
    > > The advantage to using APR or some other widely used library, is
    > > it has been peer reviewed and tested in production by many people
    > > all over the world.

    >
    > So has hashlib. I made a trivial adjustment last year. Before
    > that the last bug report was in 2002. I have had no further bug
    > reports. You obviously haven't looked at it.


    My question is: is hashlib solely your code?

    I am not doubting the quality of your code... but its different to
    have a solution from a single developer available from their personal
    site, than to have one from a relatively large and reputable
    organization like the apache foundation.

    If the code was at least available as a package in the major linux
    distros (redhat, suse, debian, etc) than I would be fine with it.

    Regards,
    Ivan Novick
    http://www.0x4849.net
    Ivan Novick, Dec 26, 2007
    #9
  10. CBFalconer Guest

    Ivan Novick wrote:
    > CBFalconer <> wrote:
    >> Ivan Novick wrote:
    >>
    >>> The advantage to using APR or some other widely used library, is
    >>> it has been peer reviewed and tested in production by many people
    >>> all over the world.

    >>
    >> So has hashlib. I made a trivial adjustment last year. Before
    >> that the last bug report was in 2002. I have had no further bug
    >> reports. You obviously haven't looked at it.

    >
    > My question is: is hashlib solely your code?
    >
    > I am not doubting the quality of your code... but its different to
    > have a solution from a single developer available from their personal
    > site, than to have one from a relatively large and reputable
    > organization like the apache foundation.
    >
    > If the code was at least available as a package in the major linux
    > distros (redhat, suse, debian, etc) than I would be fine with it.


    You are being ridiculous. The source is there, you can evaluate
    the quality for yourself.

    Before I wrote it I discussed the interface with various members of
    the c.l.c group. This resulted in some interface modifications.
    The code is mine. I issue the licenses. Everybody gets a free GPL
    license.

    --
    Merry Christmas, Happy Hanukah, Happy New Year
    Joyeux Noel, Bonne Annee, Frohe Weihnachten
    Chuck F (cbfalconer at maineline dot net)
    <http://cbfalconer.home.att.net>



    --
    Posted via a free Usenet account from http://www.teranews.com
    CBFalconer, Dec 26, 2007
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. beersa

    EXCEL VBA Caluculation ERROR

    beersa, Mar 16, 2006, in forum: Java
    Replies:
    3
    Views:
    394
    TechBookReport
    Mar 16, 2006
  2. Piet
    Replies:
    0
    Views:
    1,049
  3. Patrick Burke
    Replies:
    0
    Views:
    443
    Patrick Burke
    Jul 21, 2004
  4. Domenico Discepola

    win32::ole and excel VBA macro conversion: SmallScroll

    Domenico Discepola, May 5, 2004, in forum: Perl Misc
    Replies:
    10
    Views:
    550
    Charlton Wilbur
    May 9, 2004
  5. Replies:
    1
    Views:
    514
Loading...

Share This Page