I have an Excel book that is a template. In worksheet Data, cell A1 has the following formula:
=getTemplateVersion()
This function retrieves a custom document property with the version number of the template. I am trying to update the template version and then update the contents of cell A1. I am using the following function:
Sub setVersion(version As String)
'class for setting and retrieving template version property
Dim vm As New VersionManager
Dim currentVersion As Variant
Dim sht As Worksheet
'retrieves template version property value
currentVersion = vm.getVersion
'sets template version property value
vm.setVersion version
For Each sht In ThisWorkbook.Sheets
sht.calculate
Next
End Sub
When this function concludes, the value of A1 has not changed. But when I click on A1, then click on the formula, then press Enter, the value of A1 updates. So the version property is being updated, but it's just not updating on the sheet when sht.calculate is called.
Is there a way to update the value of this UDF-calling formula programmatically? I'd prefer not to have to click into this cell every time I run the version update function.
