Solved

I need to pass a named range to a macro

Posted on 2010-09-08
10
1,424 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:programmertim
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Author Comment

by:programmertim
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
And it works for both =Test(A1:D2) and =Test(MyRange)  without the quotes.

0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

RIA (Rich Internet Application) tools are interactive internet applications which have many of the characteristics of desktop applications. The RIA tools typically deliver output either by the way of a site-specific browser or via browser plug-in. T…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Viewers will learn various types of data validation for different data types in Excel 2013.
Viewers will learn the basics of the new Quick Analysis feature in Excel 2013.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now