Object Reference to Workbook and NamedRange with VBA Code
Hi
I have the below VBA Code being used in LibreOffice Calc. This code is working well when the code is placed into a module in the document.
I have moved this code to module in "My Macros & Dialogs" hoping to use this on every Cal Document as needed. However, the same code returns an error.
Is there a way I can get a reference to the NamedRange and with which I can use this Range Object for further processing?
Any help would is appreciated!
Option VBASupport 1
Option Explicit
Function GetReferenceName1(RefCell as Range)
Dim WB as Workbook
Dim WS as Worksheet
dim NamedRange as Range
dim sRefName as String
Set WS = RefCell.Worksheet
Set WB = Workbooks(ThisComponent.Title)
Set NamedRange = WB.Names("PersonNames").RefersToRange
GetReferenceName1 = NamedRange.Cells(RefCell.Row,1)
End Function
Sorry, but why not
=CELL("ADDRESS";OFFSET(PersonNames ;ROW(I7)-1;0;1;1))? It seems to me that there are slightly fewer characters in such a recordHi John,
Thank you for taking a look my question. The code I posted is smaller part of the entire code. I am running a custom function where I pass different named ranges as parameters along with other parameters. This function is supposed to take the cellRef and the namedrange name, column as input and return back the value from the namedrange.
I understand it. I don't understand why a macro is better than regular VLOOKUP(), OFFSET() or INDEX().
I don't know how Excel treats the matter, but Calc distinguishes different scopes.
Sub littleMess()
doc = ThisComponent
sheet = doc.Sheets.getByName("Sheet1")
named1 = sheet.NamedRanges
rg1 = named1.getByName("my_rg1")
Print rg1.Content
End Sub
@JohnSUN: Using VLOOKUP, OFFSET is probably my best bet here to get rid of getting the NamedRange references from the MyMacros. My function has other features where it looks for different ranges as well. As my first languge isn't English, I unable to explain it well.
@Lupp: It appears that the only way for me to is to re-write some of my code using Basic + API. I have pasted an image of my code with the error message. The code was working well without any issue when the code is placed in the file, but it is not working when the code is moved to "MyMacros" section.
@ramarao Don't try to get an active book in such a horrible way
Set WB = Workbooks(ThisComponent.Title)- look at the first line in @Lupp's example codedoc = ThisComponent.This is quite enough.