Announcement

Collapse
No announcement yet.

XLL autorefresh cells?

Collapse
X
 
  • 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:
    http://www.hellobasic.com/cgi-bin/fo...989992;start=0

    It's *very* simple.
    hellobasic

  • #2
    Edwin,
    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? "

    Comment


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

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

      3)
      The MS xll addin is here:
      http://www.hellobasic.com/trials/addins/test1.xll

      Type in a cell: =MyTime()

      4)
      The OO dll is here:
      http://www.hellobasic.com/trials/addins/test2.dll
      hellobasic

      Comment


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

        Yukk
        hellobasic

        Comment


        • #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(.....)
          Eigil
          Last edited by E Dingsor; 26 Mar 2009, 12:41 PM.

          Comment


          • #6
            Ha cool, see:

            http://books.google.nl/books?id=ABUS...um=1&ct=result

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

            Comment


            • #7
              Edwin,
              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.

              Eigil

              Comment


              • #8
                Yes.

                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..
                hellobasic

                Comment


                • #9
                  Edwin,
                  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!

                  Eigil

                  Comment


                  • #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..
                    hellobasic

                    Comment


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

                      Comment


                      • #12
                        Edwin,
                        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.

                        Eigil

                        Comment


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

                          Comment


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

                            Edwin,

                            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" ,""

                            Thanks,

                            Blair,

                            Comment


                            • #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.
                              hellobasic

                              Comment

                              Working...
                              X