• oleobject o_WorkBook long ll_Row,ll_RowCount,ll_NewRow String ls_SheetName o_WorkBook = Message.PowerObjectParm ll_RowCount = o_WorkBook.Worksheets.Count FOR ll_Row = 1 TO ll_RowCount ...
oleobject o_WorkBook
long ll_Row,ll_RowCount,ll_NewRow
String ls_SheetName

o_WorkBook = Message.PowerObjectParm

ll_RowCount = o_WorkBook.Worksheets.Count

FOR ll_Row = 1 TO ll_RowCount
ls_SheetName = o_WorkBook.WorkSheets(ll_Row).Name

ll_NewRow = dw_1.InsertRow( dw_1.RowCount() + 1 )

dw_1.SetItem(ll_NewRow,"bxh",ll_Row)    //表序号
dw_1.SetItem(ll_NewRow,"bm",ls_SheetName) //表名
NEXT

展开全文
• Sub Maco1() For i = 1 To Sheets.Count Cells(i, 1) = Sheets(i).Name Next End Sub
Sub Maco1()

For i = 1 To Sheets.Count

Cells(i, 1) = Sheets(i).Name

Next

End Sub

有的excel禁用宏：

展开全文
• Dim i As LongDim lCount As LongDim n As String Dim k As Long Dim j As Long lCount = Application.ThisWorkbook.Worksheets.Count ...For k = 1 To 5 For j = 1 To 3 Dim change As St...

Dim i As LongDim lCount As LongDim n As String
Dim k As Long
Dim j As Long
lCount = Application.ThisWorkbook.Worksheets.Count

For k = 1 To 5    For j = 1 To 3                Dim change As String
change = Cells(k, j).Value        If change = "B2" Then                        Dim s As String        Dim s0 As String        s0 = "="        s = ""        s = s + s0                For i = 2 To lCount            n = Application.ThisWorkbook.Worksheets(i).Name                        Dim s1 As String            Dim s2 As String            Dim s3 As String            Dim s4 As String            s1 = n            s2 = "!"            s3 = change            s4 = "+"            s = s + s1 + s2 + s3 + s4                    Next            s = Left(s, Len(s) - 1)            Cells(k, j).Value = s                    End If    NextNext

转载于:https://www.cnblogs.com/cuihongyu3503319/archive/2011/09/05/2168016.html
展开全文
• compared all possibilities with a long test sheet: 0,140625 sec for lastrow = calcws.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).row 0 sec for iLastRow = calcws.Cells(rows.count, "a").End(....

compared all possibilities with a long test sheet:
0,140625 sec for
lastrow = calcws.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).row
0 sec for
iLastRow = calcws.Cells(rows.count, "a").End(xlUp).row
and
numofrows = calcws.Cells.SpecialCells(xlLastCell).row
0,0078125 sec for
lastrow = calcws.UsedRange.rows.count Do While 1 If calcws.Cells(lastrow, 1).Value = "" Then lastrow = lastrow - 1 Else Exit Do End If Loop
I think the favourites are obvious...

Today’s author, Chad Rothschiller, a Program Manager on the Excel team, is back with a follow up from his previous post on VBA and Excel performance.
I want to start off this post by thanking everyone who sent in their examples in response to my January request. It is incredibly helpful to be able to look at what you all are doing with Excel! Not only did I see a huge variety in how Excel is being used, you also pointed out various tips and tricks for writing fast VBA code in Excel.
In this post I’m going to share with you the most important performance tips I know about. There are tons of sites, pages, and people who are experts as well on this subject, have performed their own tests, and shared their results and ideas. If you think I missed an important concept for how to optimize Excel VBA performance, or if you’ve got a valuable comment or link to share, please feel free to post here so everyone can benefit. Thanks!

Turn Off Everything But the Essentials While Your Code is Running
This optimization explicitly turns off Excel functionality you don’t need to happen (over and over and over) while your code runs. Note that in the code sample below we grab the current state of these properties, turn them off, and then restore them at the end of code execution.
One reason this helps is that if you’re updating (via VBA) several different ranges with new values, or copy / pasting from several ranges to create a consolidated table of data, you likely do not want to have Excel taking time and resources to recalculate formulas, display paste progress, or even redraw the grid, especially after every single operation (even more so if your code uses loops). Just one recalculation and one redraw at the end of your code execution is enough to get the workbook current with all your changes.
Here’s some sample code that shows how and what to shut off while your code runs. Doing this should help improve the performance of your code:

‘Get current state of various Excel settings; put this at the beginning of your code
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
displayPageBreakState = ActiveSheet.DisplayPageBreaks ‘note this is a sheet-level setting
‘turn off some Excel functionality so your code runs faster
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False ‘note this is a sheet-level setting
‘after your code runs, restore state; put this at the end of your code
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState ‘note this is a sheet-level setting

Here’s a quick description for each of these settings:
Application.ScreenUpdating: This setting tells Excel to not redraw the screen while False. The benefit here is that you probably don’t need Excel using up resources trying to draw the screen since it’s changing faster than the user can perceive. Since it requires lots of resources to draw the screen so frequently, just turn off drawing the screen until the end of your code execution. Be sure to turn it back on right before your code ends.
Application.DisplayStatusBar: This setting tells Excel to stop showing status while False. For example, if you use VBA to copy/paste a range, while the paste is completing Excel will show the progress of that operation on the status bar. Turning off screen updating is separate from turning off the status bar display so that you can disable screen updating but still provide feedback to the user, if desired. Again, turn it back on right before your code ends execution.
Application.Calculation: This setting allows you to programmatically set Excel’s calculation mode. “Manual” (xlCalculationManual) mode means Excel waits for the user (or your code) to explicitly initiate calculation. “Automatic” is the default and means that Excel decides when to recalculate the workbook (e.g. when you enter a new formula on the sheet). Since recalculating your workbook can be time and resource intensive, you might not want Excel triggering a recalc every time you change a cell value. Turn off calculation while your code executes, then set the mode back. Note: setting the mode back to “Automatic” (xlCalculationAutomatic) will trigger a recalc.
Application.EnableEvents: This setting tells Excel to not fire events while False. While looking into Excel VBA performance issues I learned that some desktop search tools implement event listeners (probably to better track document contents as it changes). You might not want Excel firing an event for every cell you’re changing via code, and turning off events will speed up your VBA code performance if there is a COM Add-In listening in on Excel events. (Thanks to Doug Jenkins for pointing this out in my earlier post).
ActiveSheet.DisplayPageBreaks: A good description of this setting already exists: http://support.microsoft.com/kb/199505(Thanks to David McRitchie for pointing this out).

Read/Write Large Blocks of Cells in a Single Operation
This optimization explicitly reduces the number of times data is transferred between Excel and your code. Instead of looping through cells one at a time and getting or setting a value, do the same operation over the whole range in one line, using an array variable to store values as needed.
For each of the code examples below, I had put random values (not formulas) into cells A1:C10000.
Here’s a slow, looping method:

Dim DataRange as Range Dim Irow as Long Dim Icol as Integer Dim MyVar as Double Set DataRange=Range(“A1:C10000”)
For Irow=1 to 10000   For icol=1 to 3     MyVar=DataRange(Irow,Icol)  ‘Read values from the Excel grid 30K times     If MyVar > 0 then        MyVar=MyVar*Myvar ‘ Change the value        DataRange(Irow,Icol)=MyVar  ‘Write values back into the Excel grid 30K times     End If    Next Icol Next Irow

Here’s the fast version of that code:

Dim DataRange As Variant Dim Irow As Long Dim Icol As Integer Dim MyVar As Double DataRange = Range(“A1:C10000”).Value ‘ read all the values at once from the Excel grid, put into an array
For Irow = 1 To 10000   For Icol = 1 To 3   MyVar = DataRange(Irow, Icol)   If MyVar > 0 Then     MyVar=MyVar*Myvar ‘ Change the values in the array     DataRange(Irow, Icol) = MyVar   End If Next Icol Next Irow Range(“A1:C10000”).Value = DataRange ‘ writes all the results back to the range at once

Note: I first learned of this concept by reading a web page by John Walkenbach found here:http://www.dailydoseofexcel.com/archives/2006/12/04/writing-to-a-range-using-vba/
A previous Excel blog entry by Dany Hoter also compares these two methods, along with a selection / offset method as well: http://blogs.msdn.com/excel/archive/2008/10/03/what-is-the-fastest-way-to-scan-a-large-range-in-excel.aspx
…which leads me to my next point.
Avoid Selecting / Activating Objects
Notice that in the above-referenced blog post, the selection method of updating a range was the slowest. This next optimization minimizes how frequently Excel has to respond to the selection changing in the workbook by minimizing the selection changing as much as possible.
Range Example: Again, see the Excel blog post quoted above. It demonstrates that using selection is the slowest of the 3 methods discussed for reading and writing to ranges.
Shapes Example: Setup: I have 40 shapes on a sheet, and I want to write “Hello” in each of them.
Using the slower “selection” method, the code looks like this:

For i = 0 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(i).Select
Selection.Text = “Hello”
Next i

The much faster method is to avoid selection completely and directly reference the shape:

For i = 0 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(i).TextEffect.Text = “Hello”
Next i

The concepts illustrated by the examples above can also be applied to objects other than Ranges and Shapes.
Note: I first learned of this concept, in the context of shapes, by reading a web page by Ron de Bruin found here:http://www.rondebruin.nl/shape.htm
Related Performance Paper
See the “Improving Performance in Excel 2007” paper on MSDN: http://msdn.microsoft.com/en-us/library/aa730921.aspx
This is a fairly detailed and comprehensive paper that introduces the bigger grid and increased limits in Excel 2007, and primarily focuses on Excel calculation performance and debugging calculation performance bottlenecks. There’s also a short section on how to write faster VBA macros.
Other Performance Optimizations
While the above optimizations are what I consider the most important, there are a few other “honorable mention” optimizations I will mention briefly for you to consider.
Consider the performance gains by implementing your code’s functionality via XLL / C-API. An overview and supporting materials for the SDK can be found here: http://msdn.microsoft.com/en-us/library/bb687827.aspx .
Declare variables with explicit types to avoid the overhead of determining the data type (repetitively if used in a loop) during code execution.
For simple functions used by your code in high frequency, implement them yourself in VBA instead of using the WorksheetFunction object.
Use Range.SpecialCells() to scope down the number of cells your code needs to work with.

转载于:https://www.cnblogs.com/xpvincent/p/5218470.html
展开全文
• 一个在Excel中使用VBA将所有sheet中的数据和sheet信息汇总到总表中的例子，使用VBA编写，在第一个sheet中的宏check，使用时需要打开excel的宏安全，执行时会在合计页填充所有其他sheetsheet名称，链接，编号，合计...
• VBA开发时，对工作表的引用有两种： Sub test() Sheets("人员").Activate ' 使用sheet1的名字“人员”进行引用，需要始终与sheet名称保持一致 Sheet1.Activate ' 直接引用sheet1，不受工作簿中sheet的名称的影响...
• 简单的Excel操作V1（获取Sheet名和Sheet中第一列列名），源码中含有详细注释，大家共同学习共同进步。V2将增加增删改查功能，大家若有什么问题或想Excel操作中新增功能，可发邮件到kw13202@gmail.com，只要有空...
• 今天来记录一下如何引用Sheet 如果所有的操作都是在当前的Sheet完成的话，是不需要引用Sheet的。 举个例子，当前有Sheet1和Sheet2两个Sheet，假设当前选中的是Sheet2，我们输入： Range("A1") = "...
• 今天项目上有个应用，获取指定Excel文件下的所有sheet的名称以及当前sheet中指定单元格的值，并把他们写到固定的sheet中去，看了下，文件比较多，而且每个文件sheet的个数比较多，也不一样，所以打算写个程序来帮...
• 今天项目上有个应用，获取指定Excel文件下的所有sheet的名称以及当前sheet中指定单元格的值，并把他们写到固定的sheet中去，看了下，文件比较多，而且每个文件sheet的个数比较多，也不一样，所以打算写个程序来帮...
• ## vba copy sheet

千次阅读 2019-07-11 17:33:54
本人最近利用记录宏的方式得到一条VBA语句以实现copy sheet 的功能. 语句如下: Sheets("mainREPORT").Copy Before:=Sheets(4)         ----------------------------------------------------------...
• 直接在设计模式中添加控件，然后在VBA中调用该控件的代码： 开发——》设计——》插入——》选择Form控件的 CheckBox ActiveSheet.Shapes.Item(1).OLEFormat.Object.Value 上面代码也可以调用图形等对象。获取的...
• ## Excel VBA - Sheet对象

万次阅读 2011-10-27 12:29:02
工作表引用  Worksheets(3).Select 这里面的3...Worksheets("Sheet3").Select 这里面的Sheet3是Name Sheet3.Select 这里面的Sheet3是CodeName  数组  For Each Rng in Range("A1:J10") For Each Sht i
• Excel VBA代码怎么样获取或修改当前工作表的表名 获取: Sub get_sheetname() sheetname = ActiveWindow.ActiveSheet.Name Debug.Print sheetname(即时窗口打印,使用Ctrl + G能调出即时窗口,MsgBox消息盒子) End Sub ...
• 获取某个sheet表最后一行的行号，对于使用vba进行循环查找是必经的过程： 通过我最近的学习发现2中方法进行使用，现在分享如下： 如下表格示例： 方法一： Public Sub test() Dim lastRow As Long lastRow =...
• 先写一个公用的方法，只需要传入各系统的连接字符串，与SHEET名 代码注释很详细 需注意的是，时间字符串 需要用Format(X, "yyyy-mm-dd HH:MM") 进行转换，否则会显示为数值 Private Sub GetData(strCn As String...
• Sub countrownum() Dim i, num, rownum num = Worksheets.Count For i = 2 To num 'Worksheets(i).Select rownum = WorksheetFunction.CountA(Worksheets(i...
• 获取当前sheet列表 Sub GetSheetList() Dim sht As Object '//... Dim s As String '// 追加sheet名 Dim i As Long '// loop count '// 追加sheet Call Sheets.Add(After:=Sheets(Sheets.Count)) s = "AddSheet
• 从上一篇文章我们已经可以获取某单元格的数据，现在我们只要执行：MsgBox VarType(curCell)就可以获取到类型，完整代码如下： Sub Macro1() ' ' Macro1 Macro ' 宏由 cq 录制，时间: 2016/08/02 x = Sheets("Sheet1...
• 确定sheet页：worksheets(“sheet1”) “sheet1”为页名称，对应写下你设置好的名称即会定位到你选择的sheet页。 确定单元格：range("$A$1")这即表明定位到A列第一行，如果想用变量来定义则可以写成range(“A”&...
• 每个月份产生一个sheet页的数据，Sheet名称命名格式格式固定YYYY.MM 需要处理逻辑： 1、每年的1月份不累计以前月份的数据；其它月份累计上个月的历史数据 1.1 如果在历史数据中获取不到数据，则累计值需要返回...
• EXCEL-VBA：通过SQL查询数据Sheet中的数据
• fname = ActiveSheet.Name-------获取当前sheet页的名称 Sname = "" & fname & "" start_coll = ActiveCell.Column----------获取选中单元格的所在位置：列 start_roww = ActiveCell.Row--------...
• Worksheets("sheet1").Range("B1").Value = InteriorColor(Range("G2")) End Sub Function InteriorColor(CellColor As Range) Application.Volatile InteriorColor = CellColor.Interior.Color End Function
• 在EXCEL中,对数据进行定位操作,必然要用到数据区域的最大行和最大列,代码如下: columnmax = Range("IV1").End(xlToLeft).Column '取得1行的最大列数 columnmax =cells(i,columns.count)....rowmax = Sheet1.Range(...
• 获取工作簿中所有表的名称和地址
• 如果需要对用户指定的文件进行操作，可以使用GetOpenFilename方法打开Excel内置的【打开】对话框，获取用户选择的文件名，此过程并不需要真正打开文件，示例代码如下。 Sub SelectFile() Dim vntFilename As ...
• 开始参考了 VBA获取某文件夹下所有文件和子文件目录的文件中的代码，按照此方式获取的结果有问题。 问题1 无法获取目录中包含“.”的子目录 '-- 获得所有子目录 Do Until i > k f = Dir(file(i), vbDirectory)...
• 有时候办公室同事从系统上导出表，当数据量过大的时候，系统都会拆分成多个sheet来导出，每个sheet的表结构一致。他们需要将sheet合并，这样以便做筛选，做透视表。 这里帮他们搞了个xlsm小工具，直接将一个工作簿中...

...