Solved

I need to pass a named range to a macro

Posted on 2010-09-08
10
1,463 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 500 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Six Sigma Control Plans

728 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