Roedd wedi ysgrifennu:
I am working on a workbook which has the VBA editor lock by a
password. so I can't write or see VBA code.
but what I would like to do is being able to write some quick VBA in
the function bar.
Imagine in E5 a cell with text and an Hyperlink, in F5 an empty cell.
in F5, I would like to put something like
=Range('E5').Hyperlinks(1).Address
and then being able to see the Hyperlink of E5 in the F5 cell.
but I can't get a Range object in the cell itself.
is it possible? how?
I was intrigued by this idea. Not sure how useful it is, but here goes.
Get hold of the Microsoft Script Control. It should still be available from
a dark and dusty corner of MSDN.
Drop a script control onto a sheet in your workbook. I used Sheet1 and kept
the default name for the instance of the script control.
Then, add the following code to a standard module in your workbook:
Public Function VisualBasic(Code As String) As Variant
Dim scrExecute As ScriptControl
On Error GoTo ERR_LOC
With Sheet1.ScriptControl1
.Reset
.Language = "VBScript"
.Timeout = "10000"
.AddObject "Application", ThisWorkbook.Application, True
.AddCode "Function EvalMe()" & vbLf & "EvalMe = " & Code _
& vbLf & "End Function"
VisualBasic = .Run("EvalMe")
End With
Exit Function
ERR_LOC:
VisualBasic = Sheet1.ScriptControl1.Error.Description
End Function
Using this, I was able to run very simple
VB code inside a worksheet
function such as:
=visualbasic("cells(2).value")
=visualbasic("cells(3).offset(2,1).value")
and
=visualbasic("cells(5,5).Hyperlinks(1).Address")
Note that you can't use embedded quote characters as the code stands, but
I'm sure you could work around that easily.
HTH
--
Rob