Loading a Row of data by =FunctName(B2:Z2)

[JustJoe]

Hello, Long time lurker here, I thought I would be able to find this quick but I can't. I haven't programmed in VBA in a few years and I'm trying to get back into it. I'm trying to make a user defined function to return a value inside of a row of data that's "unusual". My question, how do I get the row of data into an array so I can manipulate it. I want to call the function from the excel sheet similar to any other function (like =SUM(b2:Z2) )

I can't find sample code to do this, and I thought it would be the most elemental code out there.

Please help. I'd really like to start doing this again.

 

[JustJoe]

Oh... I should make mention, although my example is SUM, all of my data is Strings. anything from a "-" to a serial number "A124b2"

 

[Firefly2012]

Hi

Do you mean something like this:

Function myFunction(r As Range)
  Dim cell As Range
  For Each cell In r
   'do stuff
  Next cell
  myFunction = <something>
End Function

Example use:

=myFunction(B2:Z2)

 

[JustJoe]

Wow... to quote the Office Max commercial, that was easy. Yes that worked very well for the simple function I had to write.

I really need to look into this, because I don't understand how you related cell to r. BUT, it worked! And I was able to do it and not take up my whole Sunday. Thank you very much FireFly (Whedon fan?)

 

[Firefly2012]

. Thank you very much FireFly (Whedon fan?)

You're welcome!

Firefly was (and still is) the best sci-fi series ever bar none