"Tushar Mehta" wrote:
A few comments on the NoteShow function.
First, Notes are superseded by Comments.
Second, is there a reason why you just don't have rngCell.NoteText
rather than all the gyrations regarding workbook and worksheet and
Continuation of my query, I have another one. Even though the code
perfectly reproduces the formula contained in the cell it only shows the cell
reference of the formula. But I have given a name for the cell and the said
name is not displayed but the cell reference (row & col no) only has been
displayed. Is it possible to amend the formula /code to make it display the
exact contents of the formula (name given to the cell) rather than the cell
reference wherever the formula contains names. i.e instead of =L122*L119/1000
the formula should show -26.93*_36347/1000
range address?
Third, according the XL VBA help, NoteText returns at the most 255
characters at a time. It is the developer's responsibility to loop and
(re)create the complete note.
--
Regards,
Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
In article .com,
says...
I've a workbook containing this function. Download my
Excel_Calendar.xls from:
http://www.pvv.org/~nsaa/excel.html#21
I've named it ShowFormula:
=ShowFormula(A1)
In the same manner I've made a function showing the note in a Cell
=NoteShow(A1)
The Functions
Public Function ShowFormula(ByRef rngCell As Range) As String
'************************************************* *****************************
'
' Macro ShowFormula created 2004-08-13 by Nikolai Sandved
)
'
' Description: This Function returns the Formula in the input Cell
rngCell as
' text
'
' Input:
' rngCell - A cell
'
' Output
' RankStatistical - Formula in the input Cell rngCell as text
'
'************************************************* *****************************
'** Error Control
On Error GoTo ErrorHandle
ShowFormula = rngCell.Formula
Exit Function
ErrorHandle:
'** Set the return error objects
MsgBox ("Error code:" & CStr(Err) & Chr(13) & Chr(13) _
& "Further Description: " & Error$ & Chr(13) _
& "In Custom function ShowFormula")
End Function
Public Function NoteShow(ByRef rngCell As Range) As String
'************************************************* *****************************
'
' Macro NoteShow created 2004-06-21 by Nikolai Sandved )
'
' Description: This Function returns the Note in the input Cell rngCell
'
' Input:
' rngCell - A cell Range
'
' Output
' ShowNote - Returns the Note as text from the Cell reference given
'
'************************************************* *****************************
'** Error Control
On Error GoTo ErrorHandle
'** Define variables
Dim wbm As Workbook 'The active workbook
Dim wsWithNote As Worksheet 'The worksheet with the Note
Dim strWorkbookName As String 'The Workbook with the Note
'** Declare variables
strWorkbookName = Mid(rngCell.Address(1, 1, 1, 1), _
InStr(rngCell.Address(1, 1, 1, 1), "[") + 1, _
InStrRev(rngCell.Address(1, 1, 1, 1), "]") - _
InStr(rngCell.Address(1, 1, 1, 1), "[") - 1)
Set wbm = Workbooks(strWorkbookName)
Set wsWithNote = wbm.Sheets(rngCell.Worksheet.Name)
NoteShow =
Application.WorksheetFunction.Clean(wsWithNote.Ran ge(rngCell.Address).NoteText)
'Cleans
Set wsWithNote = Nothing
Set wbm = Nothing
Exit Function
ErrorHandle:
'** Set the return error objects
MsgBox ("Error code:" & CStr(Err) & Chr(13) & Chr(13) _
& "Further Description: " & Error$ & Chr(13) _
& "In Custom function NoteShow")
'Cleans
Set wsWithNote = Nothing
Set wbm = Nothing
End Function