如何显示内置Excel功能的进度条？ [英] How can I display a progress bar for a built-in Excel function?

问题描述

我有一个Excel表设置，大约40,000个值需要通过用户窗体进行排序。为此，我使用...

  myTable.Sort.SortFields.Add范围（strSortBy），xlSortOnValues，xlDescending，xlSortNormal 
 myTable.Sort.Apply 
  

...其中myTable是表，strSortBy是标题的列我想排序。

现在，尽管Excel的闪电般快速的排序功能，有了这么多的数据，它开始花费足够长的时间（几秒钟），我'd想向用户提供某种进度反馈。我已经在Excel中创建了几个用户形状的进度条，但是由于Sort是一个内置的 Excel函数，我看不到任何明显的方式显示或确定已完成的百分比。

这是一个迷失的原因吗？还是有一些方法来确定内置函数的进度？

非常感谢任何帮助。

解决方案

我喜欢使用内置的状态栏功能来显示进度。

示例：

 应用程序.StatusBar =重命名单元格
 
当count2 <=（count1）
 DoEvents 
 Length = Len（Range（C& CStr（count2） ）
 Newlength = Length  -  2 
 newID = Left（Range（C& CStr（count2））。Value，Newlength）
范围（C& CStr（count2） ）.Value = newID 
 count2 = count2 + 1 
 newID = vbNullString 
 Application.StatusBar =Editing Cell：& count2& of& count1&  
 Wend 
  

在示例中，我可以循环使用单元格，缩短长度的每一个。在这样做的时候，我显示程序所在的单元格，因为可能需要很长时间。

希望这有帮助！

I have an Excel table set up with around 40,000 values that need to be sorted via a userform. For this, I used...

myTable.Sort.SortFields.Add Range(strSortBy), xlSortOnValues, xlDescending, xlSortNormal
myTable.Sort.Apply

...where myTable is the table and strSortBy is the header of the column I want to sort by.

Now, despite Excel's lightning-fast sorting capabilities, with this amount of data it starts taking long enough (several seconds) that I'd like to provide the user with some kind of progress feedback. I've made several userform progress-bars in Excel, but since Sort is a built-in Excel function, I can't see any obvious ways to show or determine the percentage completed.

Is this a lost cause? Or is there some way to determine the progress of built-in functions?

Any help is greatly appreciated.

解决方案

I like to use the status bar function, which is built in, to display progress. It is very simple, yet powerful.

Example:

Application.StatusBar = "Renaming Cells"

While count2 <= (count1)
    DoEvents
    Length = Len(Range("C" & CStr(count2)).Value)
    Newlength = Length - 2
    newID = Left(Range("C" & CStr(count2)).Value, Newlength)
    Range("C" & CStr(count2)).Value = newID
    count2 = count2 + 1
    newID = vbNullString
    Application.StatusBar = "Editing Cell: " & count2 & " of " & count1 & "."
Wend

In the example, I'm able to loop through cells, shortening the length of each one. While doing so, I display the cell the program is on, since it can take a long time.

Hope this helps!

这篇关于如何显示内置Excel功能的进度条？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！