Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

I need to pass a named range to a macro

Posted on 2010-09-08
10
Medium Priority
?
1,498 Views
Last Modified: 2013-12-21
I have an OpenOffice Basic function that accepts a named range as a parameter.  It was originally an Excel sheet and worked fine.  However, all I get from OpenOffice is "#NULL!".  I'm guessing the error is with trying to pass the range, not with the routine, as I have ripped it down to the following test routine and it still doesn't work.

Function Test(RangeObject as Object) as String
      Test = "Yes"
end function

There's not a lot in there that can fail.
0
Comment
Question by:programmertim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 33643689
You have to make sure that you are passing your subroutine a valid OOo Base object

For instance the Code Below works with a named range of Num1

-Bear

Sub Main
oSheet = ThisComponent.CurrentController.ActiveSheet
source = oSheet.getCellRangeByName("Num1")
print test(source)
End Sub

Function Test(RangeObject as Object) as String
      Test = "Yes"
end function

Open in new window

0
 
LVL 1

Author Comment

by:programmertim
ID: 33643744
One clarification.  I am trying to call the macro from within a Cell.  It works fine called from another macro, but if I type

=Test(Num1)

in a cell, I get #NULL! as the result showing in the cell, without the macro actually being called.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 33643893
That is because your Function is expecting an Object, but when you pass it via a function on a cell it sends it as a string.  Try this code:

Function Test(RangeName as String) as String
	Dim oSheet as Object
	Dim oRange as Object

	' Get Access to This Sheet
	oSheet = ThisComponent.CurrentController.ActiveSheet
	
	' Grab the Range
	source = oSheet.getCellRangeByName(RangeName)
	
	Test = "Yes"
end function

Open in new window

0
The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

 
LVL 1

Author Comment

by:programmertim
ID: 33658199
I can't type =Test(Num1) into a cell even if the Test routine expects a string.  I have to pass =Test("Num1").  So I am not passing the range, I am passing the range name.  If it was passing a string, I would receive errors indicating that the properties and methods on the object don't exist (ie, RangeObject.getCellByPosition doesn't exist if rangeobject is a string).  Instead, I get #NULL! in the cell without the macro being called at all.
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 33658462
I guess I am confused as to what you want to do.  In you original post you mentioned named ranges.  The second code will do function in your spreadsheet based off a named ranged.  However, if you are wanted to pass the actual range like A1:A4, your original code should work:
=test(A1:A4)
If you do want to pass a named range, you must pass the name of the range.  Hence =Test("Num1").  Num1 is a string that refers to the actual range of A1:A4 or however you have defined Num1.  Num1 itself is not a range but just a name for the range.  Therefore you have to pass the routine the name of the range so that it can look up the range.

You will need to provide some more clarification so that we can assist.  Thanks.
0
 
LVL 1

Author Comment

by:programmertim
ID: 33659165
Should work, but doesn't.  I am converting from MSExcel, which does support it.  However, neither =Test(Num1) nor =Test(A1:A4) will actually work in OpenOffice  They both insert #NULL! into the cell, without actually calling the macro.

It sounds like the answer is "There is no way to pass a range to a macro in a cell formula".  Is that correct?
0
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 2000 total points
ID: 33661948
I am testing in Open Office 3.2 and the formulas are working just fine.  If I put values in cells F1, F2 and F3 and then name that range as Num1.  Then I put the following code in as a Macro:

Function Test(RangeName as String) as String
    Dim oSheet as Object
    Dim oRange as Object

    ' Get Access to This Sheet
    oSheet = ThisComponent.CurrentController.ActiveSheet
   
    ' Grab the Range
    source = oSheet.getCellRangeByName(RangeName)
   
    Test = "Yes"
End Function
Then in Cell A1 I put = Test("Num1"), I get YES in cell A1

If instead I put this code into the Macro:

Function Test(RangeObject as Object) as String
      Test = "Yes"
end function
Then in cell A1 I put =Test(F1:F3), I get YES in cell A1

So yes you can pass a range to a Macro.  I have shown that.  If you want to post your entire Macro, I can take a look at that I see If I can find what is failing in your code.
0
 
LVL 1

Author Comment

by:programmertim
ID: 33668373
I think I must have something else going on then.  In an effort to demonstrate, I created a brand new spreadsheet, created a new Module1, and created a routine in the module as follows:

Function Test(oObjName as String) as String
      Test = "Yes"
end Function


I then put =Test(A1:D2) in a cell.  It give me Err:512 in the cell.  I then defined a named range named MyRange over A1:D2 and was getting ready to enter =Test(MyRange) when I realized that my first cell was in my range.  So I typed =Test(A1:D2) outside the range (Cell A4) and it also gave me Err:512 (I could be wrong on the number, but something like that).  I then deleted the contents (default) of cell A1.  I got "Object variable not set", supposedly on the function declaration line.  I received it twice, once for each of the cells I currently have calling Test.  Once I had hit Ok twice, "Yes" appeared in both cells.  I entered some text in the range, got the same error twice, then the cells blanked out.  Not #NULL!, not Err:512, and not Yes.  I have now entered and deleted text all over the place and can't get yes to come back.  I am attaching the file, but I imagine it will work for you.
test.ods
0
 
LVL 1

Author Comment

by:programmertim
ID: 33668409
Nevermind, that's just me being high on something, I suppose.  Properly making oObjName an object works.  It still doesn't explain why my other macro is failing, but I'll just have to start that sheet from scratch or something instead of trying to upgrade from Excel.
0
 
LVL 1

Author Comment

by:programmertim
ID: 33668413
And it works for both =Test(A1:D2) and =Test(MyRange)  without the quotes.

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
Introduction to Processes
Starting up a Project

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question