Solved

I need to pass a named range to a macro

Posted on 2010-09-08
10
1,437 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
  • 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Non-recursive backtracking, using a stack 1 135
creating threads in delphi 1 106
Advice in Xamarin 21 80
"Black Box" Testing of Control System Software 2 52
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 connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.

810 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