No announcement yet.

XLL autorefresh cells?

  • Filter
  • Time
  • Show
Clear All
new posts

  • XLL autorefresh cells?

    I have this xll and would like to Excel update all the cells having my functions.
    I would like this on user demand.
    Is this possible?

    Today i wrote a dll for use with OpenOffice and it's really VBA having a simple declare.
    The cell using the function is updated when the document is reloaded.
    It's not that bad if it's automatically updated in Excel as well but for now on demand would be better.


    For the OO version you can take a peek here:
    Great domain names provide SEO, branding, and a memorable experience for your users. Get a premium domain today.

    It's *very* simple.

  • #2
    Could you attach a zip with the complete code?
    Reason I ask is that I knew Open office was working on allowing VBA type in some version (and heard that version is out, but not sure how far back), and was looking forward to the day that I could supplement or replace any VB code with actual PB code

    Only problem is, I usually do not to macro's, but if I could find a simple example that solves both M$ and Open Office (or at least Open Office, I will find the link to M$) it could be well worth expanding.

    PB has potential for a TON of power....but its my mind that is limited as to how to tap that POWER
    Engineer's Motto: If it aint broke take it apart and fix it

    "If at 1st you don't succeed... call it version 1.0"

    "Half of Programming is coding"....."The other 90% is DEBUGGING"

    "Document my code????" .... "WHYYY??? do you think they call it CODE? "


    • #3
      The code for OO is shown on my board.
      Just compile to dll.

      My question is about VBA for MS Office, it does not update itself or on demand.

      The MS xll addin is here:
      Great domain names provide SEO, branding, and a memorable experience for your users. Get a premium domain today.

      Type in a cell: =MyTime()

      The OO dll is here:
      Great domain names provide SEO, branding, and a memorable experience for your users. Get a premium domain today.


      • #4
        This does work:
        =MyTime() & LEFT(NOW();0)



        • #5
          I've looked at the code and you will have to make the code (inVBA) as volatile .Search for Volatile in VBA, The suggestion "= Mytime() & NOW(....) makes is volatile in Excel. I.e NOW() is defined as a volatile function in Excel.
          If you are making a real XLL file, then just register the function with the volatile flag in the Excel4 api function REGISTER(.....)
          Last edited by E Dingsor; 26 Mar 2009, 11:41 AM.


          • #6
            Ha cool, see:

            Financial Applications using Excel Add-in Development in C/C++ is a must-buy book for any serious Excel developer.Excel is the industry standard for financial modelling, providing a number of ways for users to extend the functionality of their own add-ins, including VBA and C/C++. This is the only complete how-to guide and reference book for the creation of high performance add-ins for Excel in C and C++ for users in the finance industry. Steve Dalton explains how to apply Excel add-ins to financial applications with many examples given throughout the book. It also covers the relative strengths and weaknesses of developing add-ins for Excel in VBA versus C/C++, and provides comprehensive code, workbooks and example projects on the accompanying CD-ROM. The impact of Excel 2007’s multi-threaded workbook calculations and large grids on add-in development are fully explored. Financial Applications using Excel Add-in Development in C/C++ features: Extensive example codes in VBA, C and C++, explaining all the ways in which a developer can achieve their objectives. Example projects that demonstrate, from start to finish, the potential of Excel when powerful add-ins can be easily developed. Develops the readers understanding of the relative strengths and weaknesses of developing add-ins for Excel in VBA versus C/C++. A CD-ROM with several thousand lines of example code, numerous workbooks, and a number of complete example projects.

            I will check this tomorrow.
            And indeed, i register currently without a bang symbol. (!)


            • #7
              Just a word of caution. Making a function volatile may take up much of your PC's resources. If an Excel cell formula is referencing your volatile function - "even in 5th degree of remoteness" it will force all cells to be recalculated. A recalculation somewhere in your worksheet will trigger your volatile function to recalculate. At work we had some database queries that ran via XLL functions and a date parameter that clogged up the system since every re/calculation forced the queries to be triggered again. Instead I made a date function which is not volatile and we avoided the problems.



              • #8

                Today i tried a lot of stuff, running calculate from VBA didn't update the sheet and also not F9.
                I just need a single update (onload) for the whole sheet and MS doesn't do that.
                I believe i can trigger this by obtaining the 'formula' and run it again, i would need to to enum cells or ranges.
                To much work..


                • #9
                  Have you tried to mimic a F2(Sendkey ...) behavior via VBA i.e like you if you double click a cell with your function in and then hitting the return key? That should trigger a recalc event in your worksheet. FYI: I'm almost done with converting the new C++ Excel 2007 SDK header files(main ones) to Powerbasic. Will be posted when I get time to go the last leg on that work!



                  • #10
                    No need, the bang symbol seems to work.
                    But it seems not to work for my previous xlsx file.
                    I created a new file and entered the same command.
                    Odd is that when you click FX (next to the cell bar on top) it shows result, calculate always.
                    My custom fucntion simply shows the return value (date+time)

                    It seems there is still a difference.

                    At this time it seems to work, i am ok..


                    • #11
                      FYI, i have replaced the xll (above)
                      Using "=MyTime()" is now updated on doc load and F9.


                      • #12
                        At work we are migrating to 2007 and I have experienced some odd behaviour with XLL files. Most of that can be pinpointed to "Trusted zone" settings in 2007. My gut feeling is it will work as before if you include the path to your xll file in the trusted area/zones in Excel 2007.



                        • #13
                          Good to know.
                          + maybe i can sign the xll later on?
                          At the end our users may want to use this stuff.


                          • #14
                            Where do you put the Bang(!) Symbol?


                            I am a current user of Pwrdev and assume that you modified the test.xll in it.
                            So, my question is where do you put the ! in the registration file to make it volitile?

                            VD_XLL_RegisterFunction "MyTime", "C", "MyTime", "time", "1", sGroupname, "", "", "Returns the current time" ,""




                            • #15
                              Not "C" but "C!"
                              It's possible you may have remove the cell content and reenter the statement.
                              It didn't seem to work right away.
                              If you use a new sheet you can test if this works.