VBA Returning a Range

[Blammdon1]

I can't find this anywhere on the net and it may sound stupid but, how do i return an actual range from a user created function so that another function say the MIN function can use that value to look at that range. (I mean like an actual Range, one that turns blue in excel not the values in that range)
Example:
******************************************************
Public Function Example(Column1 as String, Row1 as Integer, Column2 as String, Row2 as Integer )

Example= ?????? 'What should this return be?


End Function

Then use this in the min function
MIN(Test("G",3,"G",7)
It should return the minimum value in the Range "G3:G7"

Thanks in advance

 

[Kenneth Hobson]

By your function, to you mean the original or the one that I posted?

Post your file to a site like mediafire.com and post the link to it if you want us to look at it.

Not sure why you expect =Test to mean anything other than in another function. Try =G1:G7, and you will see that it only returns the first cell's value.

 

[Blammdon1]

I have some numbers in column H right now. Three of them are negative. This formula:

=COUNTIF(Test("h",4,"h",25),"<0")

returns 3, which is correct. Now I add 1, 10, and 100 in the column just to the right, next to each of the negative numbers. This formula:

=SUMIF(Test("h",4,"h",25),"<0",I4:I25)

returns 111, which is correct. So does this one:

=SUMIF(Test("h",4,"h",25),"<0",Test("i",4,"i",25))

Well i would still like it to return the actual range value but whatever, im giving up for the weekend...

 

[iliace]

G1:G7 does not return an actual range, either - it returns the values in that range.

Here, take a look here:

sample workbook

There are formula examples in column B.

 

[Scott Huish]

Why do you want to type the range in this way?

Why not just use MIN(G3:G7) instead of MIN(TEST("G",3,"G",7))

 

[Tom Urtis]

I want it to return the actual range,
Example:
Test("G",1,"G",7) would have to return the usable range G1:G7

There are at least 2 ways you can do this, one using the Cells syntax and the other using the more familiar column and row syntax.

Example of the Cells syntax:

Public Function myRangeCells(Row1 As Long, Column1 As Long, Row2 As Long, Column2 As Long) As String
myRangeCells = Cells(Row1, Column1).Address & ":" & Cells(Row2, Column2).Address
End Function

Formula example:
=myRangeCells(3,4,6,8)
translated, row 3 column 4 (which is D3) to row 6 column 8 (which is H6)
returns
$D$3:$H$6



Most people are not accustomed to entering references that way, so you can use the column letter approach as follows:

Public Function myRangeLetters(Column1 As String, Row1 As String, Column2 As String, Row2 As String) As String
myRangeLetters = Range(Column1 & Row1 & ":" & Column2 & Row2).Address
End Function

Formula example:
=myRangeLetters("A",6,"J",7)
(notice the column letters in quotes)
returns
$A$6:$J$7


That is what it appears you asked for, so when using it beyond that, you might need Indirect in native formulas.


Also, a caveat, with 4 elements to enter, just the right way, including the order they should be enetered, the quotes (if the latter UDF i sused) and the logic of upper left to lower right addresses), that is usually too much for most users to grasp, so you are asking for failure if you expect a user to do this the correct way every time, I'd opt instead for an Application InputBox or more foolproof user friendly method.

 

[Blammdon1]

Thanks for your help Tom I'm really glad i got an answer to this, and just to let you know the only users that will be using this function are all experienced Excel users and will be familiar with the functions parameters and how to input them the correct way. Thanks again!

 

[Blammdon1]

Why do you want to type the range in this way?

Why not just use MIN(G3:G7) instead of MIN(TEST("G",3,"G",7))

Well in the end i want this function to be dynamic so i can take in different variables that will be the same as a range such as taking in an integer to represent the row number and maybe an integer from another function to reference another row number. This function would just help me out by allowing different types of inputs and then spitting out the type that Excel could use in other functions or applications. Also I'm writing this for a co-worker and he said Macros were out of the question otherwise i would've done that.

 

[Tom Urtis]

Well i would still like it to return the actual range value but whatever, im giving up for the weekend...

Ha - - working on the weekend after all, I see.

Thanks for your help Tom I'm really glad i got an answer to this, and just to let you know the only users that will be using this function are all experienced Excel users and will be familiar with the functions parameters and how to input them the correct way. Thanks again!

No problem, thanks for the follow-up.


Edit - -

Also I'm writing this for a co-worker and he said Macros were out of the question otherwise i would've done that.

BTW, if macros are out of the question, the UDFs will still require a medium or lower security setting, which if it's medium means the users will be prompted for a trusted source Enable Macros message.

 

[Kenneth Hobson]

So, what you really wanted was the string address of a range? One can easily do this in many easy ways.

1. ="G"&3&":"&"G"&7
2. =CONCATENATE("G",3,":","G",7)

Or a simile UDF does does this simple concatenation.