香港新浪網 MySinaBlog
Emily | 25th Mar 2006, 11:27 AM | General | (686 Reads)

Select > 8,192 non-contiguous cells in macro

CAUSE

This behavior occurs if you select more than 8,192 non-contiguous cells with your macro. Excel only supports a maximum of 8,192 non-contiguous cells through VBA macros.

Typically, if you try to manually select more than 8,192 non-contiguous cells, you receive the following error message:
The selection is too large.
However, when you use a VBA macro to make the same or a similar selection, no error message is raised and no error code is generated that can be captured through an error handler.

WORKAROUND

To work around this behavior, you may want to create a looping structure in your VBA macro that handles less than the maximum 8,192 cells.

 

Microsoft

Article ID 832293

Please read the article "SpecialCells limit problem" by Ron de Bruin
See also this page from David McRitchie


Emily | 24th Mar 2006, 01:08 AM | WB & WS | (1274 Reads)

如何得到一個公式中引用的單元格

比如一個單元格 C1 的公式是 =SUM(E4:E7) +A1

'---------------------------------------------------------------------------------------
' Module    : Module1
' DateTime  : 24/3/2006 01:11
' Author    : Emily
' Purpose   : 得到一個公式中引用的單元格
'---------------------------------------------------------------------------------------
Sub GetPrec()
Dim Strng As Variant
Dim i As Long

' VBA's Precedents (同一頁). 

' MsgBox Range("C1").Precedents.Address     ' Also works
Strng = Split(Range("C1").Precedents.Address, ",")   ' C1=SUM(E4:E7) +A1
For i = LBound(Strng) To UBound(Strng)
    Debug.Print Strng(i)
Next i
End Sub

 '

 

 (閱讀全文)

Emily | 18th Mar 2006, 10:57 AM | General | (1431 Reads)

即時視窗 (VBE Immediately Window) 與 ThisWorkBook

相信大家懂得代碼 ThisWorkbook.FullName 與 ThisWorkbook.Close

但是有沒有在即時視窗執行

 (閱讀全文)

Emily | 16th Mar 2006, 21:39 PM | WB & WS | (1139 Reads)

儲存格位置改變後單一的圖片停留在右下角儲存格

請首先在 Sheet1 插入適當的圖片,然後在 VBE Sheet1 插入以下代碼。

 (閱讀全文)

Emily | 14th Mar 2006, 22:38 PM | WB & WS | (2111 Reads)

列出同目錄下 Excel 檔案資料

'---------------------------------------------------------------------------------------
' Module    : Module1
' DateTime  : 14/3/2006 21:55
' Author    : Emily
' Purpose   : List Files information in Current WorkBook Path
'---------------------------------------------------------------------------------------


Option Explicit

Sub FilesInfo()
' Set reference to Microsoft Scripting Runtime
Dim x As Integer
Dim myFso As Scripting.FileSystemObject
Dim myFiles As Scripting.Files
Dim myFile As Scripting.File
Set myFso = New Scripting.FileSystemObject
Set myFiles = myFso.GetFolder(ThisWorkbook.Path).Files
x = 2
With Sheet1
    Cells(1, 1) = "檔案名稱"
    Cells(1, 2) = "製作日"
    Cells(1, 3) = "最後存取日"
    Cells(1, 4) = "最後更新日"
    Cells(1, 5) = "大小 (Kb)"
    For Each myFile In myFiles
        If myFso.GetExtensionName(Path:=myFile) = "xls" Then
            With myFile
                Cells(x, 1) = .Name
                Cells(x, 2) = .DateCreated
                Cells(x, 3) = .DateLastAccessed
                Cells(x, 4) = .DateLastModified
                Cells(x, 5) = (.Size / 1024)
                x = x + 1
            End With
        End If
    Next

    Columns("A:E").EntireColumn.AutoFit
End With
Set myFiles = Nothing  
Set myFso = Nothing
End Sub


Emily | 13th Mar 2006, 00:56 AM | 雜談 | (1084 Reads)

Internet Explorer Developer Toolbar

Brief Description
Beta 2
The Microsoft Internet Explorer Developer Toolbar provides a variety of tools for quickly creating, understanding, and troubleshooting Web pages. This version is a preview release and behavior may change in the final release.
The Internet Explorer Developer Toolbar provides several features for exploring and understanding Web pages. These features enable you to:

-- Explore and modify the document object model (DOM) of a Web page.
-- Locate and select specific elements on a Web page through a variety of techniques.
-- Selectively disable Internet Explorer settings.
-- View HTML object class names, ID's, and details such as link paths, tab index values, and access keys.
-- Outline tables, table cells, images, or selected tags.
-- Validate HTML, CSS, WAI, and RSS Web feed links.
-- Display image dimensions, file sizes, path information, and alternate (ALT) text.
-- Immediately resize the browser window to a new resolution.
-- Selectively clear the browser cache and saved cookies. Choose from all objects or those associated with a given domain.
-- Choose direct links to W3C specification references, the Internet Explorer team weblog (blog), and other resources.
-- Display a fully featured design ruler to help accurately align and measure objects on your pages.
 (閱讀全文)

Emily | 11th Mar 2006, 23:06 PM | General | (812 Reads)

Invisible Basic

Invisible Basic is an Excel Add-in that replaces the VBA code within an Excel Workbook with obfuscated (very hard to understand) but functionally equivalent code. Simply "Save Invisibly" to share your Excel solutions...without sharing their source code.

More Details: http://invisiblebasic.sourceforge.net/

chijanzen 也有介紹 , 還有轉換結果 http://www.vba.com.tw/plog/post/1/278


Next