How to Pass ranges in functions so correct cell in range is passed?

[Damckinleysr]

I have a row with the Year and Month in each cell in the format 201401 (YYYYMM).

201401 is in A1 The progression is 201401 201402 201403 ..... 201512.

If I named the Range "YYYYMM" (=Sheet1!$A$1:$X$1) Excel will automatically put in the proper value- if I put =YYYYMM in B1 it will display 201401. If I fill right for 23 cells each cell will have =YYYYMM and Excel will put in the proper value for that column. For example: K2 will display as 201411.

I have a function that counts the number of Friday's in the month
Fuction (YYYYMM as String) as Integer

if put = GetFridaysinMonth (K1) in K2 it clearly passes 201411 as the string and returns 4.

To make the worksheet more readable I want to use the name range YYYYMM as an argument. If I put = GetFridaysinMonth (YYYYMM) in K2 how can I make the function know the value of YYYYMM is 201411?



Fuction GetFridaysinMonth (YYYYMM as range) as Integer


How do I figure out it is in col K and value is 201411? If have tried byVal, etc and cannot figure out right combination. Any clues?


End Function

Thanks

 

[mikerickson]

You have a function, its argument is a string.

1) From your description, it sounds like what is in the cell is an Excel Serial Date formatted to YYYYMM format. That format changes what one sees in the cell, but not the underlying value.

2) One nice thing about UDF's is the automatic conversion that is provided by Excel.

If you have a function

MyFunction (someString as String) As String

And you want to use it on the string in cell A1, you don't have to pass the range as an argument, you put the cell reference in the worksheet formula
=MyFunction(A1) and excel does the rest.

As to your function, look into what is actually in the cell. is it a number or a a string or a date with an unusual format. One quick way to tell is to format one of those cells as General and look at it (including whether its aligned left or right).

 

[Damckinleysr]

Thanks - I pass the argument the way you recommend MyFunction(A1) already. The problem is the spreadsheet has dozens of functions with multiple variables and is not very readable.

Instead of Naming the Range YYYYMM I could have named it Year_Month.

The actual function is looking more like this -=Number_Events(D$1,D$9,D$3,$B$20) as all the arguments are in Ranges - I think something like =Number_Events(Year_Month, Barn , Poss_Days, Day_Type) is much more readable. I cannot figure out how to pass a Range Name like this and have the function figure out what cell in the range it should be.

Thanks for the help




[TABLE="width: 476"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Rental Rates Per Event[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Weekdays[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Fridays[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Damckinleysr]

this is similar to what I am trying to do
Name Col A Lenth
Name Col B Width
Col C is the formula = Width * Length
Excel will calculate all the cells in Col C correctly - Even though formula is = Width * Length in all cells
Col A Col B Col C
[TABLE="width: 192"]
<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]Length[/TD]
[TD="width: 64"] Width
[/TD]
[TD="width: 64"] Area
[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]95[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

Instead of using the formual = Width * Length in each cell I want to do = AreaCalculation (Width, Length) and have it work in a similar way.