Object Reference to Workbook and NamedRange with VBA Code

asked 2021-02-22 15:57:55 +0100

ramarao gravatar image

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
edit retag flag offensive close merge delete

Comments

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

JohnSUN gravatar imageJohnSUN ( 2021-02-22 17:01:25 +0100 )edit

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.

ramarao gravatar imageramarao ( 2021-02-22 17:38:21 +0100 )edit

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

JohnSUN gravatar imageJohnSUN ( 2021-02-22 18:00:33 +0100 )edit
  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

Lupp gravatar imageLupp ( 2021-02-22 18:08:32 +0100 )edit

@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 gravatar imageramarao ( 2021-02-23 16:07:03 +0100 )edit

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

JohnSUN gravatar imageJohnSUN ( 2021-02-23 16:50:36 +0100 )edit