軟體:Excel
版本:2000,2002,2003(Version tested with)
前幾篇提到OpenOffice Basic 疑似無法傳入 Range (?)
順便來觀摹一下Excel Function如何傳入Range(並判斷是否空白)
判斷空白應該是 If myRng = "" then ...
我試了類似 Is Empty 發現是不對的
大概是因為該變數的確有東西 是個Range
在GOOGLE function range vba的時候看到了這兩個程式碼
功能是彌補CONCATANATE的不足，
合併範圍內所有的字串。
http://www.vbaexpress.com/kb/getarticle.php?kb_id=580 簡單版
http://www.vbaexpress.com/kb/getarticle.php?kb_id=817 多功能版
其實多功能版的代碼不會比較多，都是註釋而已 XD
多功能版才有判斷空白的功能
===============================
Option Explicit
Sub CatenateIt()
'replace A1:G2 below with your own range
'DEMO: concatenate A1 to G2
MsgBox Catenate(Range("A1:G2"), " ")
End Sub
'<< Function to concatenate cells in a range >>
Public Function Catenate(MyRange As Range, _
Optional Delimiter As String) As String
Dim Cell As Range, N As Long
N = 1
'go thru MyRange cell by cell and concatenate
For Each Cell In MyRange
If N = MyRange.Cells.Count Then
'we don't need a delimiter after last cell
Catenate = Catenate & Cell
Else
'otherwise we do need a delimiter
Catenate = Catenate & Cell & Delimiter
End If
N = N + 1
Next Cell
Set Cell = Nothing
End Function
===============================
Option Explicit
Function ConcRange(Substrings As Range, Optional Delim As String = "", _
Optional AsDisplayed As Boolean = False, Optional SkipBlanks As Boolean = _
False)
' Concatenates a range of cells, using an optional delimiter. The
' concatenated strings may be either actual values (AsDisplayed=False)
' or displayed values. If NoBlanks=True, blanks cells or cells that
' evaluate to a zero-length string are skipped in the concatenation
'
' Substrings: the range of cells whose values/text you want to
' concatenate. May be from a row, a column, or a "rectangular" range
' (1+ rows, 1+ columns)
'
' Delimiter: the optional separator you want inserted between each item
' to be concatenated. By default, the function will use a zero-length
' string as the delimiter (which is what Excel's CONCATENATE function
' does), but you can specify your own character(s). (The Delimiter can
' be more than one character)
'
' AsDisplayed: for numeric values (includes currency but not dates),
' this controls whether the real value of the cell is used for
' concatenation, or the formatted displayed value. Note for how dates
' are handled: if AsDisplayed is FALSE or omitted, dates will show up
' using whatever format you have selected in your regional settings for
' displaying dates. If AsDisplayed=TRUE, dates will use the formatted
' displayed Value
'
' SkipBlanks: Indicates whether the function should ignore blank cells
' (or cells with nothing but spaces) in the Substrings range when it
' performs the concatenation. If NoBlanks=FALSE or is omitted, the
' function includes blank cells in the concatenation. In the examples
' above, where NoBlanks=False, you will see "extra" delimiters in cases
' where the Substrings range has blank cells (or cells with only spaces)
Dim CLL As Range
For Each CLL In Substrings.Cells
If Not (SkipBlanks And Trim(CLL) = "") Then
ConcRange = ConcRange & Delim & IIf(AsDisplayed, Trim(CLL.Text), _
Trim(CLL.Value))
End If
Next CLL
ConcRange = Mid$(ConcRange, Len(Delim) + 1)
End Function
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 114.47.35.27