Probably dead obvious...

[wossname]

Why don't spreadsheet cells that call VBA functions automatically update themselves when you change other cells?

Is there any way to overcome this?

I have autocalculate switched on and my macros are enabled, whats stopping the (public) function (in a module) from being called?

I know the function works because if I double click it and immediately hit enter, it updates to the correct value.

[Ecniv]

MS Excel??

How are you calling the sub?
Are you using WorkBook_SheetChange?

[wossname]

Excel yeah.

I call the func like this:
"=CollateAndCalcShare()"
(in the cell directly)

Not using sheetchange.

Any ideas ?

[Ecniv]

Um...

Nope no idea. Tried putting that function (which returns "Test") and it didn't run. Just got the #Name?

I take it the function when run in the VBA window works fine. But the function doesn't run from the spreadsheet. And it doesn't error. No idea . quirk of MS Excel...

Which version are you running (being curious) ?

EDIT:
Version Excel 2000 here...
Shoved the code into a module (not the ThisSheet module) and it works fine.
Is it something in your code? (not likely) or perhaps a re-install of excel is required.
Tested with this code:

Public Function CollateAndCalcShare() As String
    CollateAndCalcShare = "Test" & Cells(2, 3)
End Function

The function is in cells(2,2) (B2)

[wossname]

Excel v9.0.4402 SR1

I know the function definitely does work. It's just that excel won't call it. I've tried everything from adding 0 to the result to try to jog it into action and even linking another cell after the calling cell to fetch the data from it but nothing happens.

[Ecniv]

Does the function I posted earlier work?
I have the same version excel here at work so I could run a test to see whether its a crap install?
Post the code or a zip file? (if it doesn't breech data protection stuff)

[wossname]

The cell in question is marked "Contractor Share", I want it to update itself when "Actual Cost" is altered.

This is really annoying.

[Ecniv]

I don't think you are gonna like this...
but...

CollateAndCalcShare = CDbl(CStr(CalculateShare(Range("B18").value, Range("B22").value, b)))

Replace that line... The bit in bold was the part I added. Seems to run then. Debug fine without it but doesn't like it except on the entering first time unless that is in there, then works fine (cstr? cdbl? your output of the function is double...)



Hope that fixes it

[wossname]

I tried what you said but it made no difference at all.

I took out both cdbl and cstr because they appear to be redundant. Still doesn't work though.

VBA/Ofice are the worst things ever. *HATE*

[Ecniv]

Word/excel vba is crap against access vba (seems more polished)
Access vba is different and probably worse than vb
vb is worse compared to vb.net

I checked the help files and there is no mention of using custom macros in cells as functions. Which means you may need to put it in the worksheet change to get it to run.

Sorry. Excel is pants. How come you didn't use a vb.net project for this? its pretty simple isn't it??

[wossname]

Sorry. Excel is pants. How come you didn't use a vb.net project for this? its pretty simple isn't it??

Funny you should say that. I actually did the initial prototype in VB.net just so I could get the stupid algorithm right (which was invented by a typically stupid accountant).

However, the people that are intended to use this system do not have the framework on their machines. So it falls to excel.

[wossname]

Sorted it out now. Bunged the call into the calculate event instead.

Cheers for the help.

[Ecniv]

lol (sigh)
Attached a zip file to my last post.. take a look and see what you think?