Bullet point in Excel

Excel 並未提供Bullet point ，要輸入的話有三種方法:
1. 按住鍵盤的alt 鍵，然後依次按下數字鍵盤的1,4,9，鬆開alt 鍵

2. 插入特殊字符，sub – set 選擇latin -1.即可見到Bullet point.

3. 利用text box. Textbox 提供與word 一樣的模式，方便插入bullet point

article clipper remember Bullet point in Excel
 
2011
11/10
分类
Excel
发表评论

Add Menu Items In The VBA Editor

Daily Dose of Excel近日更新了插件 – vbhelper. 這是一個非常好用的插件,可以快捷地在vba中創建class. 具體功能詳見此介紹.

image Add Menu Items In The VBA Editor

然而,該插件是在英文版的Excel中開發的,因此,假如所用的Excel為其他國家語言的版本的話,將會報錯. 原因很簡單,該插件會在Tools(中文版為工具)的最底插入一個Menu.不幸的是,中文版Excel中’菜單條’已經本地化將菜單翻譯成中文’工具’,代碼無法找到該項Menu,自然就無法添加菜單了.加上,作者沒有進行任何ErrorHandling,所以用家很可能不知所措.

修復的方法很簡單,只需將

With Application.VBE.CommandBars("Menu Bar").Controls("Tools")

改為

With Application.VBE.CommandBars("菜单条").Controls("工具(&T)")

image 3 Add Menu Items In The VBA Editor

(至於如何得知應該修改為’菜單條’跟’工具(&T)’, 可以通過監視窗口, 更深入的研究,可參見此文),

假如在增加,刪減菜單欄過程中遇到誤刪Menu的情況,可以通過以下語句將菜單條重置.

Application.VBE.CommandBars("Menu Bar").Reset

更多關於VBE的進階講解,請移步該處.

article clipper remember Add Menu Items In The VBA Editor
 
2011
11/09
分类
Excel
标签
发表评论

Delete Duplicate Rows

Public Sub DeleteDuplicateRows()

'This macro will delete all duplicate rows which reside under
'the first occurrence of the row.

'Use the macro by selecting a column to check for duplicates
'and then run the macro and all duplicates will be deleted, leaving
'the first occurrence only.

Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range,rngTarget as range
Dim col as integer

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

rngTarget=ActiveSheet.UsedRange     ' your target range   ##### change here #####
col=ActiveCell.Column           ' your column         ##### change here #####

Set Rng = Application.Intersect(rngTarget, _
                    ActiveSheet.Columns(col))
Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")
N = 0
For R = Rng.Rows.Count To 2 Step -1
  'If R Mod 500 = 0 Then
  '    Application.StatusBar = "Processing Row: " & Format(R, "#,##0")
  'End If

  V = Rng.Cells(R, 1).Value

  If V = vbNullString Then
      If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then
          Rng.Rows(R).EntireRow.Delete
          N = N + 1
      End If
  Else
      If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
          Rng.Rows(R).EntireRow.Delete
          N = N + 1
      End If
  End If
Next R

EndMacro:
  Application.StatusBar = False
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  MsgBox "Duplicate Rows Deleted: " & CStr(N)

End Sub
article clipper remember Delete Duplicate Rows
 
2011
11/08
分类
Excel
标签
发表评论

Progress Bar

當VBA運行時間比較長的時候, 最好有指示告知程序在運行, 否則用戶會認為死機. 若能計算出剩餘時間的話,當然最好了. (通常計算運算時間都通過線性估算,然而,對於複雜的計算, 這並不可行.)

Progress Bar, Excel界面左下角的Status Bar都可以作為指示器. 個人認為,Status Bar不明顯,很容易會給忽略.

Status Bar:

For i=1 to 100
      Application.StatusBar = i
next i
Application.StatusBar = False

Progress Bar:

下列Form

  1. 都利用Label的長度展示程序完成的百分比.
  2. 在更新完Label,都需要用DoEvents去重構界面

設計1

這個是最好的設計,有提示運行時間,剩餘時間,計算量
image Progress Bar

設計2

只顯示完成百分比
image 3 Progress Bar

設計3

此需要DLL的支持,不推薦
image 4 Progress Bar

設計4

這個Bar用了新思路,利用Microsoft ProgressBar Control 6.0,加載Controls插件
image 5 Progress Bar

image thumb Progress Bar

設計5

Andy Pope將Progress Bar進行了演化,引入了多種形式. 基本理念就是與普通bar一致,計算出現位置,DoEvents
image thumb 3 Progress Bar

article clipper remember Progress Bar
 
2011
11/07
分类
Excel
标签
发表评论

Choose a column or row from a range

從一個已知range中選取某一列,或者某一行

Dim rng as range, one as range

For each one in rng.columns(2).cells
      debug.print one
next one
article clipper remember Choose a column or row from a range
 
2011
11/06
分类
Excel
发表评论

How to change an addin into a workbook

有時候爲了特別的目的,需要將一個addin變為workbook,可以通過如下步驟:

1. 打開VBE,找到你要轉換的xla文件

image How to change an addin into a workbook

2. 點擊ThisWorkbook

image 3 How to change an addin into a workbook

3. 將IsAddin屬性改為False

4. 點擊文件 – 關閉并返回到Microsoft Excel

image 4 How to change an addin into a workbook

5. xla將會自動變為一個workbook, 此時只需要另存為就可以了

article clipper remember How to change an addin into a workbook
 
2011
11/04
分类
Excel
标签

发表评论

How to download a file using VBA part 2

Excel本身並不具備下載文件的功能,除了前面所述方法外,我們還可以通過ADODB.Stream去下載文件.首先要建立一個XMLHTTP的object,向服務器發送一個請求,然後再對返回的信息進行二進制編碼,寫到本地磁盤.

有關XMLHTTP, 可參閱JP的有關文章.

Sub test()
Dim oHttp, objStream
Set oHttp = CreateObject("Microsoft.XMLHTTP")
oHttp.Open "GET", "http://www.example.com/abc.zip", False
oHttp.send

Set objStream = CreateObject("ADODB.Stream")
objStream.Type = 1
objStream.Open
objStream.Write oHttp.ResponseBody
objStream.Savetofile "C:\abc.zip", 2
objStream.Close

End Sub
article clipper remember How to download a file using VBA part 2
 
2011
11/03
分类
Excel
标签
发表评论

How to download a file using VBA

在Excel中下載文件,需要運用到win32API的URLDownloadToFile.
(注: 還可以通過模擬鼠標動作,進行下載)

Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Dim Ret As Long

Sub Sample()
    Dim strURL As String
    Dim strPath As String

    '~~> URL of the Path
    strURL = "http://www.example.com/abc.zip"
    '~~> Destination for the file
    strPath = "c:\abc.zip"

    Ret = URLDownloadToFile(0, strURL, strPath, 0, 0)

    If Ret = 0 Then
        MsgBox "File successfully downloaded"
    Else
        MsgBox "Unable to download the file"
    End If
End Sub
article clipper remember How to download a file using VBA
 
2011
11/01
分类
Excel
标签
发表评论

10 tips when using VBA Recordset objects

1. 查閱傳遞

Loop一個Recordset之前,先檢查Recordset object是否為空.

If Not(rs.BOF And rs.EOF) Then

BOF為真,假如現時指針位置在第一條記錄之前. EOF為真,假如現時指針位置在最後一條記錄之後.

於是,只要兩者同時為真,證明Recordset為空集.

2. 查看record count

ADO Recordset

If Not(rst.BOF And rst.EOF) Then

假如recordcount=-1,表示為空集

3. 不能一直使用rs.MoveNext

使用rs.MoveNext之前,需先確認current record在BOF與EOF之間

If rs![fieldname] = value Then

  ...do something...

rs.MoveNext

正確做法:

  Do While Not rs.EOF

  If rs![fieldname] = value Then

    ...do something...

  End If

  rs.MoveNext

Loop

4. 死循環

切記假如rs.MoveNext

5. 使用Recordset.find時,避免no match error

用rs.find(criteria)搜索record時,假如沒有搜到任何record的時候,會報錯. 於是,利用rs.NoMatch,確保有結果之後,再進行操作.

rs.Find searchcriteria

If Not rs.NoMatch Then

  ...do something...

End If

6. 強制新加入的Record為現時Record

當往一個Recordset中加入,一條新的Record后,需要強制更新.

rs.Update

rs.Bookmark = rs.LastModified

7. ADO的Find不支持IS

當用ADO搜索NULL的時候,不能使用下述代碼,否則報錯.

rst.Find "LastName Null"

8. ADO不支持AND

下面語句會報錯

rs.FindNext "SupplierID = 10 And CategoryID = 4"

需改為Filter

rs.Filter = "SupplierID = 10 AND CategoryID = 4"

9. 謹記close一個Recordset

rs.Close

10. 後記

Postscript: DAO vs. ADO

Within the Visual Basic world, there are two Recordset objects: Recordset and Recordset1 (new with 2007). It’s important to remember that neither the DAO nor ADO versions of these objects are interchangeable, as they support different methods, properties, and events. That can be confusing if you think ADO is just an improved or later upgrade of DAO. In fact, the two libraries solve different problems. DAO was designed specifically for the Microsoft Jet database engine. ADO was designed for OLE DB providers and can be much simpler and more flexible than DAO. However, DAO is almost always more efficient when working directly with Jet.

Know how you plan to use a Recordset and then use the most appropriate library. My best advice is to choose one library and use it exclusively when possible. The similarities and differences are too numerous to list here, but you can find information online:

Ref

article clipper remember 10 tips when using VBA Recordset objects
 
2011
10/31
分类
Excel
标签


发表评论

Get count of list items in Sharepoint

要獲取SharePoint List item的數量,當然可以通過web service來獲取,然而web service并沒有提供aggregate方法,如count等. 於是,需要將ajax返回的xml進行loop,求和.假如item比較多的時候會需要比較多的時間.

假如對於List的View沒有太大要求時,可以用Group View,利用SharePoint內置aggregate方法,如count,計算好,展示在List表頂部.通過jQuery截取就可以快速獲知item的數量.

image Get count of list items in Sharepoint

<script type="text/javascript" src="js/jquery-1.6.4.min.js"></script>
<script type="text/javascript">
<p>$(document).ready(function(){
    var groupings = [];<br>
    $("tbody[id^=titl][groupString] > tr > td").each(function(){
            groupings.push($(this).text());
    });
    $('#wrap').append('<ul></ul>');
    $.each(groupings, function(index, value){
            $('#wrap ul').append("<li>"+ value + "</li>");
    });
});
</script>
<div id='wrap'></div>
article clipper remember Get count of list items in Sharepoint
 
2011
10/30
分类
SharePoint
标签
发表评论