ExcelAsyncUtil.QueueAsMacro question

[Guido De Bouver]

hello all,

I have porblems to format a return cells properly.

The value type I am returning to Excel changes often, sometimes it is a string, sometimes a date, sometimes a value.

I am using ExcelAsyncUtil.QueueAsMacro to format it, however, I am getting some formatting errors, simply because the type changes.

My code is as follows ( in VB .NET ) ( example formatting to a timestamp for simplicity reasons )

o1=....

ExcelAsyncUtil.QueueAsMacro(

                Sub()
                    Using New ExcelSelectionHelper(ref)
                        XlCall.Excel(XlCall.xlcFormatNumber, "dd-mmm-yy hh:mm")
                    End Using
                End Sub
                )
Return o1

So when the content of the cell was a string and now it becomes a timestamp, I am trying to format the text first as a timestamp, then only pasting in the timestamp.

This gives often some strange results and formatting issues - the problems are not always the same, but appear random. Also, there is always a delay of approx 1 second between returning the value and applying the formatting.

Ideally I want want to apply the formatting only after returning the value.

thanks for your thoughts

guido

[Guido De Bouver]

I have to mention that the UDF is refreshing quite often, every few seconds the return data type can change.

I also understand that ExcelAsyncUtil.QueueAsMacro runs whenever Excel has time, so I understand that the formatting is applied after the data is sent into Excel.

So basically the main question is how can I synchronize the returning of data into Excel and the formatting of the data. 

guido

[Govert van Drimmelen]

Hi Guido,

 

I don’t know what your function is doing, but it seems like it would be hard to use further in other formulas on the sheet.

So I’m guessing it really is just a display function.

I wonder if you won’t be better off always returning a formatted string, or perhaps having an extra parameter for the function that makes it either return a formatted string or a value.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/e62abbb4-6700-4098-bb5f-2e3ea4f9dbb4n%40googlegroups.com.

[Kedar Kulkarni]

My 2 cents, in my opinion the below line is not at all needed from a function point of view. ie I believe it selects a range and restores previous selection. One does not need to select a range to format - we just need the range object to format. IF you have a volatile function then it would cause this to run again and again.

Using New ExcelSelectionHelper(ref)