Object Reference to Workbook and NamedRange with VBA Code

[ramarao]

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!

image description

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

[JohnSUN]

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 record

[ramarao]

Hi 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.

[JohnSUN]

I understand it. I don’t understand why a macro is better than regular VLOOKUP(), OFFSET() or INDEX().

[Lupp]

1. If you are talking of an error always report exactly the error message.

2. If you actually want to program for LibreOffice, switch to the API. (Very few exceptions.)

3. Don’t expect many people here to be familiar with VBA.

4. To get a named range by code is simple in LibO Basic + API.

5. 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

[ramarao]

@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.

[JohnSUN]

@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 code

doc = ThisComponent.

This is quite enough.