XLL autorefresh cells?

[Edwin Knoppert]

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/forum/YaBB.pl?board=vd;action=display;num=1237989992;start=0

It's *very* simple.

[Cliff Nichols]

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

[Edwin Knoppert]

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

[Edwin Knoppert]

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

Yukk

[E Dingsor]

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

[Edwin Knoppert]

Ha cool, see:

http://books.google.nl/books?id=ABUSU9PWUuIC&pg=PA253&lpg=PA253&dq=volatile+xll+function&source=bl&ots=ycIaJugZdN&sig=6jBSFZsKtsEWEDzCLsiJySIn7h8&hl=en&ei=qb7LSZ3EJ8vT-Qah79HMBg&sa=X&oi=book_result&resnum=1&ct=result

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

[E Dingsor]

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

[Edwin Knoppert]

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

[E Dingsor]

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

[Edwin Knoppert]

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

[Edwin Knoppert]

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

[E Dingsor]

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

[Edwin Knoppert]

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

[Blair Heinzlmeir]

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,

[Edwin Knoppert]

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.