vba - Excel UDF: use Characters object without ScreenUpdating - Stack Overflow

admin管理员组

文章数量:1357268

It appears that if an Excel VBA user-defined function makes use of a Characters object, the cell in which that function is used displays some part of that object while the VBA code is running, even if ScreenUpdating is turned off. This seriously slows down the execution. How can this be turned off or worked around?

Here is a post on Microsoft's forum where someone apparently has the same problem.

Here is a sample user-defined function to demonstrate the problem. I have repeated one line many times in order to make the effect more easily visible.

Public Function Foobar(theCell As Range) As String

    Dim i As Integer
    Dim Result As String
    
    Application.ScreenUpdating = False 'this has no effect
    
    For i = 1 To theCell.Characters.Count
        Result = theCell.Characters(i, 1).Text
        'repeat more times so the effect remains visible onscreen
        Result = theCell.Characters(i, 1).Text
        Result = theCell.Characters(i, 1).Text
        Result = theCell.Characters(i, 1).Text
        '... repeat ~10 more times
    Next i
    
    Foobar = Result
    
    Application.ScreenUpdating = True

End Function

Fill cell A1 with ~500 characters of random text and put =Foobar(A1) in cell B1. Hit enter and you see a portion of that text temporarily displayed in cell B1. The text remains there until calculation finishes and the result is displayed. This happens whether or not ScreenUpdating has been disabled.

Here is a screenshot made while the calculation was running.

What is the reason for this behavior? Is it correct to blame the slowness on VBA interfacing with Excel's display? Is there a way to bypass it?

If it is relevant, I'm using Excel 2013.

本文标签： vbaExcel UDF use Characters object without ScreenUpdatingStack Overflow