using VBA in the function bar
 

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?
please help

using VBA in the function bar
 

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

using VBA in the function bar
 

thanks for the info. but I dont have access to the VBA editor on this
spreadsheet. so I cannot paste anycode but just use what is given by
default.

in other terms, according to what you said, if I need to create extra
code to enable this function, it means its not available by default. a
bit sad, but I understand. everything can not be there ...

so I found a work around, I'm gonna use ruby instead of vba and access
the sheet remotly, much more powerful anyway.

regards

Robert Bruce wrote:
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