Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Adding a custom function to the default excel function list | Excel Programming |