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

 

[venkat1926]

TRY THIS


at the beginning put these two statements

xlCalc = Application.Calculation
    Application.Calculation = xlCalculationmanual

at the end put this statement

Application.Calculation = xlCalculationautomatic

 

[Richard Schollar]

You are recursively calling the function again with this line:

RangeToString = Replace(RangeToString, "align=center x:publishsource=", "align=left x:publishsource=")

Are you sure this is what you want?

 

[Lildail]

venkat1926:

• It's a function; I don't think you can change the calculation state in a function.

Richard Schollar:

• You are recursively calling the function again with this line:
  

  RangeToString = Replace(RangeToString, "align=center x:publishsource=", "align=left x:publishsource=")

• Am I????? I thought that using the function name in the function is simply setting the value to return (unlike C for example where you use the Return (var_to_return) syntax
• I could change this and the preceding line (using a new variable) to:

  output_temp = Input(LOF(file_number), file_number)
  output_temp = Replace(output_temp , "align=center x:publishsource=", "align=left x:publishsource=")
  RangeToString=output_temp

  But this still makes the function calculate again and again in manual calculation mode
• for completeness I've posted the (slightly) edited code at the bottom of this post.

All:
I still don't get why the calculation mode will change excel's behaviour in this instance

New proposed code (cf response to Richard Schollar):

Public Function RangeToString(rng As Range)

'declarations
    Dim file_address As String
    Dim file_number As Long
    Dim output_temp As String
'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
    output_temp = Input(LOF(file_number), file_number)
    output_temp = Replace(output_temp , "align=center x:publishsource=", "align=left x:publishsource=")    
    RangeToString=output_temp
Close #file_number
    RangeToString = Replace(RangeToString, "align=center x:publishsource=", "align=left x:publishsource=")
'free up memory
    Kill file_address
End Function

 

[xenou]

What's the purpose of this being a cell function? What is the input value from the range (or rather, what's the purpose of this rng object - it seems you are just interested in the address? -- what's the purpose of the text file)?

 

[xenou]

Note that as a test of Richard's comment it's still not fixed in your proposed code:

    output_temp = Replace(output_temp , "align=center x:publishsource=", "align=left x:publishsource=")    
    RangeToString=output_temp
Close #file_number
    [COLOR="Red"]RangeToString[/COLOR] = Replace([COLOR="Red"]RangeToString[/COLOR], "align=center x:publishsource=", "align=left x:publishsource=")
'free up memory

 

[Lildail]

1. 
   

   Note that as a test of Richard's comment it's still not fixed in your proposed code:
   
   

       output_temp = Replace(output_temp , "align=center x:publishsource=", "align=left x:publishsource=")    
       RangeToString=output_temp
   Close #file_number
       [COLOR="Red"]RangeToString[/COLOR] = Replace([COLOR="Red"]RangeToString[/COLOR], "align=center x:publishsource=", "align=left x:publishsource=")
   'free up memory

   You're entirely right. I meant to delete that line, my bad.
   
2. As to the purpose of this function: when calling it in code I use it to email out a range. I convert the range to HTML to put in these emails. I was just playing around with it when I noticed its odd behaviour when called from a cell. I'm the first to admit that I don't have a good reason to want to be able to call it from a cell. I'm just hoping to understand the odd behaviour that it exhibits from a cell and how to avoid coding this weird behaviour in the future.
   
3. On a tangent:

    [COLOR="Red"]RangeToString[/COLOR] = Replace([COLOR="Red"]RangeToString[/COLOR], "align=center x:publishsource=", "align=left x:publishsource=")

   I didn't realise this line would be recursively calling the function. I thought that this line would just treat both references to RangeToString as the string variabble that is going to be returned. So just to confirm I understand. The reference of RangeToString to the right of the equals sign is calling the function again? That surprises me because after I step through the function it goes to the next line and then through to End Function before the function starts again. ... it doesn't seem to jump to the start of the function as I thought it would do if it was recursively calling itself. Does that make sense? And I don't understand why this code is bahving differently under the two different calculation states.

New proposed code(removed that line):

Public Function RangeToString(rng As Range)

'declarations
    Dim file_address As String
    Dim file_number As Long
    Dim output_temp As String
'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
    output_temp = Input(LOF(file_number), file_number)
    output_temp = Replace(output_temp , "align=center x:publishsource=", "align=left x:publishsource=")    
    RangeToString=output_temp
Close #file_number
'free up memory
    Kill file_address
End Function

 

[shg]

I didn't realise this line would be recursively calling the function.

I don't believe it does -- it's just a garden-variety Replace function operating on the function value.

 

[Lildail]

I don't believe it does -- it's just a garden-variety Replace function operating on the function value.

That's what I thought / think too

 

[shg]

Suggest you have a read through http://www.decisionmodels.com/calcsecretsj.htm.

Does rng reference a single-cell range?