Application.calculatefull problems!

[moleman]

Hi all,

Have a simple macro to copy/paste data but need to ensure that all custom functions within the sheet have been recalculated before this occurs.

I've tried putting Application.Calculatefull at the beginning of the macro but when the macro is activated it just performs the calculations, it does not operate the lines of code beneath it - what am I doing wrong??? Here is the VBA, all that I have changed is adding Application.Calculatefull just below 'Sub Macro2()'

Sub Macro2()

With ActiveSheet.DrawingObjects("Text 6")
.Left = 100
.Top = 410
End With

Application.ScreenUpdating = False
'unhide for copying
Sheets("Certificate").Visible = True
Worksheets("Certificate").Activate
ActiveSheet.CommandButton1.Visible = True
Sheets("certificate").Copy
ActiveWindow.ActivateNext
Application.CutCopyMode = False
ActiveSheet.CommandButton1.Visible = False
Worksheets("Input 1").Activate
Sheets("Certificate").Visible = False
Cells(5, 3).Select
'update screen
Sheets("input 1").Select
With ActiveSheet.DrawingObjects("Text 6")
.Left = 2000
.Top = 0
End With
Application.ScreenUpdating = True

MsgBox "Creation of file for workflow." & Chr(13) & Chr(13) & _
"successful. Locate file.", , "Complete"
End Sub

[mdmackillop]

Hi Moleman,
Welcome to VBAX

From Help



CalculateFull Method

See Also Applies To Example (mk:@MSITStore:C:\Program\Microsoft%20Office\Office10\1053\VBAXL10.CHM::/html/xlmthCalculateFull.htm#example) Specifics
Forces a full calculation of the data in all open workbooks.


Step through the macro and determine whwere the problem is. If you canm, post a sample workbook. Manage attachments in the Go Advanced reply section.
Regards
MD

[moleman]

Thanks for the reply mdmackillop

The macro will just perform a full calculation, it will not perform any of the actions below that.

There are a lot of custom functions within the sheet, stepping through, it goes through them all and would take too long to see what it does at the end!

[Slyboots]

Volatile Functions.

A Volatile Function in a formula in a cell makes that cell be always recalculated at each recalculation even if it does not appear to have any changed precedents.
Avoid volatile functions wherever possible.
You can make a User-Defined Function (http://www.decisionmodels.com/calcsecretsj.htm) volatile by including Application.Volatile in the function code.



[quote=moleman]
Have a simple macro to copy/paste data but need to ensure that all custom functions within the sheet have been recalculated before this occurs.

I've tried putting Application.Calculatefull at the beginning of the macro but when the macro is activated it just performs the calculations, it does not operate the lines of code beneath it - what am I doing wrong??? [quote]

[moleman]

I'm not sure what you're advising there?

I have avoided using volatile functions, I'm trying to get the whole workbook to recalculate before the hardcoded values are copied across.

[Slyboots]

Your problem is that the custom functions are not recalculating. Here, you SHOULD include Application.Volatile in EACH custom function you're using, because you've got a good reason to do so.

[moleman]

Thanks Slyboots, that may be an option.

But does anyone know why fundamentally the application.calculatefull may make this macro fall over? It's been bugging me for a good while now and this seems like the first forum I've found where people have been brave enough to respond!

[Slyboots]

This may or may not help, but what happens when you manually do a "CalculateFull" (CTRL-ALT-F9). Is there any kind of pop-up or warning? That would stop the macro.

Another thing you might try is adding Application.DisplayAlerts = False as the first line of your routine.

[moleman]

Slyboots,

Nope, CTRL+ALT+F9 works fine and that is what I use personally but the spreadsheet needs to go out to people and it needs to be incorporated, it's too risky otherwise.

I've amended the functions to be all volatile and this works fine when navigating the spreadsheet but now when I activate the macro it causes the custom functions to return "#VALUE" and leaves the first object visible!!!

Application.DisplayAlerts = False doesn't change the situation.

:banghead:

[moleman]

Bump - can anyone help with this please? : pray2:

[moleman]

Ok, I've made some progress with this - one of the custom functions had an "Then End" rather than "Then End Function" statement.

HOWEVER, whilst the macro now produces the certificate, it produces #VALUE outputs, even with application.calculatefull at the beginning! It seems to recalculate but the copy/paste procedure must alter these functions somehow??

Does anyone know why this may happen or any potential solutions??

[GTO]

Understanding that the actual workbook may contain sensitive info (or maybe not), could you post an example workbook please?

Mark

[moleman]

Sorry, can't post the workbook, does contain sensitive data

[GTO]

...could you post an example workbook please?

Mark

Respectfully, to see how it's (not) working, it would be much easier to ascertain if viewing your code in action, info deleted of course.

Would like to help if possible,

Mark

[moleman]

Thank you for your time GTO, but it's just not possible to post the workbook.

If anyone else can see why this may be falling over it would be much appreciated!

[Paul_Hossler]

Dumb question, but you DID do a PasteSpecial Values, right?

And there.s no Named ranges refered to in the source that would not be in the Destination

Paul

[moleman]

Hi Paul,

Yes, pasted values only. I don't think that's the issue as when I look at the calc, it's the custom functions which are returning "#VALUE".

There's something in that VBA code that interrupts or causes the custom functions to fall over and it's driving me crazy, but I accept that without the workbook it will be difficult to resolve.

Thanks for all your help anyways!

[Bob Phillips]

Can't you strip or obfuscate the confidential data so as to post something?