function continually reclaculates (when in xlCalculationManual)

[Lildail]

Hi all,

I'm using windows XP, excel 2007.

I've written a function (VBA below). This function (to publish the HTML of a range) works nicely when called by a macro. However I'm also trying to make it work in am excel cell.

1. When I've got manual calculation the formula continually recalculates (I've gone through pressing F8, it just goes to End Function and then restarts, no errors that I can see
2. When I've got it on auto calculate (either of them) then the function does NOT run again after End Function (ie it works how I'd expect it to)
3. When calling the function through VBA code it doesn't iterate itself either

I'd really appreciate some feedback on how to solve this problem. Is it something to do with the volatility (I don't think it is though).

VBA of function:

Public Function RangeToString(rng As Range)

'declarations
    Dim file_address As String
    Dim file_number As Long
'start up code
    Application.Volatile
'Create the HTML file and publish to it
    file_address = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    With ActiveWorkbook.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=file_address, _
         Sheet:=rng.Worksheet.Name, _
         Source:=rng.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
        .AutoRepublish = False
    End With
'Read all data from the htm file into RangetoHTML
    file_number = FreeFile
    Open file_address For Input As #file_number
    RangeToString = Input(LOF(file_number), file_number)
    Close #file_number
    RangeToString = Replace(RangeToString, "align=center x:publishsource=", "align=left x:publishsource=")
'free up memory
    Kill file_address
End Function

 

[Lildail]

rng could be a single cell range but it in general won't be and whenever I'm hoping to personally use it in my day-to-day routine that I'm automating it's never a single cell reference.

Thank you for the link. I'll have a read through it in abit.

 

[shg]

If rng is not fully calculated when the function is initially invoked, it will recalculate until rng is stable. That's described at the link.

BTW, what's the point of making it volatile?