X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Recalc External Function Based On Udf, Office 2013    
post Nov 17 2017, 04:31 PM

Posts: 891
Joined: 12-November 03
From: Iowa Lot

I have a workbook from which I am importing data into Access.
The import routine is run in Access using late binding.

Some of the data imported references cells that are populated by an external function.
This function references a filter created by a UDF.

Independently, all these work. However, the UDF based filter sometimes needs recalculating before I can do the import. The results are numerical, but if the filter is not forced to recalculated, the external function returns "Invalid Data" or similar error text. Thus the import routine crashes because I'm feeding that cell into a numeric datatype.

If I open the worksheet and manually update {F9} then save it, it works. But the next day it won't, I'll have to update again.
So I want to automate this process. I've tried 3 methods, none will force the external function to update:

method 1:
'force recalc
with objSHT ' the worksheet object

.Columns(10).Calculate 'filter -- from my UDF, this will update
.Columns(11).Calculate 'external function -- this does not update
.Columns("B:C").Calculate 'based on column 11 results, thus does not update

method 2:
'force recalc
with objSHT.calculate ' the worksheet object

method 3:
'force recalc
objWB.RefreshAll ' the workbook object

I also tried pausing 20 seconds to allow the external function to calculate, to no avail. Normally, recalculating every instance using {CTRL-SHIFT-ALT-F9} does not take near that long.
PauseTime = 20 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' Yield to other processes.

And I put application.volatile in my UDF.

Any ideas?
Go to the top of the page
post Nov 20 2017, 10:08 AM

Posts: 891
Joined: 12-November 03
From: Iowa Lot

Finally got it to work.

I copied cell formula of the UDF into a variable, and copied it back to the cell, then did cell(row, col).calculate.

That still didn't work, so then since I had the workbook running in the background, I made the application visible, and I activated the worksheet.

So for sure the worksheet must be active...even though I'm referencing it through the objSHT object.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    19th October 2018 - 05:30 PM