例如選擇D3後凍結窗格 , 如何用VBA得到D3這個地址？

如果沒有隱藏 (A, B, C column),  可用

ActiveWindow.SplitRow + 1

ActiveWindow.SplitColumn  + 1

(閱讀全文)

Sub DelInImmedate()
    Debug.Print Now
    Application.SendKeys "^g ^a {DEL}"
End Sub

API Method: http://www.dailydoseofexcel.com/archives/2004/06/09/clear-the-immediate-window/

(閱讀全文)

ExcelHome Question

(閱讀全文)

ExcelHome Question

(閱讀全文)

 http://www.blog.methodsinexcel.co.uk/2007/01/19/data-encryption-workbook/

You can drop your data on to the worksheet (you can add extra worksheets too if you like) and then encrypt the data with the press of a button. You will be asked to provide a key, which you must remember. Save the file and send it out.

The receiver can then contact you for the key. When they click the decrypt button it will ask them for the key, if they enter it correctly it will return the original data. If they enter the wrong key the data will be encrypted again.

I have not tested this much, so make sure you back up your originals and remember there is not such thing a truly save data!

If you have any questions or suggestions drop me a comment.

Enjoy!

天 主 教 教 區 禮 儀 委 員 會 主 席 羅 國 輝 神 父 解 釋 說 ， 教 會 計 算 復 活 節 的 方 法 ， 其 實 是 訂 定 在 春 分 之 後 ， 首 次 月 圓 日 後 的 第 一 個 主 日 （ 星 期 日 ） ， 如 果 月 圓 那 天 剛 好 是 星 期 日 ， 那 復 活 節 則 順 延 一 個 星 期 。 好 像 以 今 年 來 說 ， 春 分 在 3 月 20 日 ， 春 分 後 首 個 月 圓 日 是 3 月 24 日 ， 其 後 的 第 一 個 星 期 日 ， 就 是 3 月 27 日 ！

(閱讀全文)

Sub RenameSheetCodeName()
On Error GoTo ErrChg
With ActiveSheet
    '.Name = "Sheet1"
    .Parent.VBProject.vbComponents(.CodeName).Properties("_CodeName") = "Sheet1"
End With
Exit Sub
ErrChg:
MsgBox Err.Number & ":=" & Err.Description, vbCritical
End Sub

Scaled-in-cell-charting

http://www.dailydoseofexcel.com/archives/2006/09/13/scaled-in-cell-charting/

(閱讀全文)

MsgBox ThisWorkbook.CalculationVersion

獲取一個數字，該數字指示上次對工作簿進行完全重新計算的 Excel 的版本。最右邊四位是計算引擎的次版本号，其他位（左邊）是 Microsoft Office Excel 的主版本。 

例子

114210 

11 是 Excel 11, i.e. Excel 2003

92512

9 是 Excel 9

0

沒有進行過計算是 0 !!!

Excel 97: 8
Excel XP: 10

(閱讀全文)

沙拉油 Blog http://blog.xuite.net/saladoil/excel/7055499

 Sub Check_VBA_Exist()
    Dim fd As FileDialog
    Dim FFs As FileDialogFilters
    Dim stFileName As String
    Dim vaItem
    Dim VBC As Object
    Dim HasCode As Boolean
    Dim wb As Workbook
    Set fd = Application.FileDialog(msoFileDialogOpen)
    With fd
        Set FFs = .Filters
        With FFs
            .Clear
            .Add "Excel文件", "*.xls;*.xla"
        End With
        .AllowMultiSelect = True
        If .Show = -1 Then
            For Each vaItem In .SelectedItems
                Application.EnableEvents = False
                Application.ScreenUpdating = False
                Set wb = Workbooks.Open(vaItem)
                HasCode = False

               '  当档案有工程密码是,出错 , amended on 30 Jul 2006

               If wb.VBProject.Protection = 1 Then    ' 判斷vbe是否保護
                    MsgBox "檔案" & Dir(vaItem) & " VBA 專案被鎖定"
                    wb.Close 0
                    ' Exit Sub
                ' End If

               Else

                For Each VBC In wb.VBProject.VBComponents
                    If VBC.Type <> 100 Then
                        HasCode = True: Exit For
                    ElseIf VBC.CodeModule.CountOfDeclarationLines < VBC.CodeModule.CountOfLines Then
                        HasCode = True: Exit For
                    End If
                Next
                If HasCode = True Then
                    MsgBox "檔案" & Dir(vaItem) & " 有宏"
                Else
                    MsgBox "檔案" & Dir(vaItem) & " 無宏"
                End If
                wb.Close 0
                Application.EnableEvents = True
                Application.ScreenUpdating = True

            End If
            Next vaItem
        End If
    End With
End Sub

Ref: http://www.officefans.net/cdb/viewthread.php?tid=39838&extra=page%3D1

 Private Sub MergeCell_AutoHeight()

Dim snCurrRowH As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
Dim stCell As String

With ActiveCell.MergeArea
    If .Rows.Count = 1 And .WrapText = True Then
        Application.ScreenUpdating = False
        snCurrRowH = .RowHeight
        ActiveCellWidth = ActiveCell.ColumnWidth
        For Each CurrCell In Selection
          MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
        Next
       .MergeCells = False
       .Cells(1).ColumnWidth = MergedCellRgWidth
       .EntireRow.AutoFit
        PossNewRowHeight = .RowHeight
       .Cells(1).ColumnWidth = ActiveCellWidth
       .MergeCells = True
       .RowHeight = IIf(snCurrRowH > PossNewRowHeight, snCurrRowH, PossNewRowHeight)
      End If
End With

End Sub

Self-describing numbers: between two digits "d" there are d digits. Almost-Langford numbers have an even number of digits and no two identical pairs of digits.

Please read http://www.research.att.com/~njas/sequences/A108116

Full List in Excel

(閱讀全文)