Excel Pass and Return a Range from a function

I have multiple functions that I use to pre-process data in my spreadsheet .  There is an example below that pre-processes  URL.  My problem is that I have over 10,000 rows (URL is one column), so I put this function in as many cells as URLs (over 10,000).  Needless to say my spreadsheet is slow.

Basically I don't know how to accept and return range data, which I believe would greatly speed it up.  How can I change the function/functions so that they accept a range (one column) and return a range (which may be one to many columns) that will show in the spreadsheet, instead of passing one cell's info at a time? (FYI - It is okay if I have to hard code the range into the function.)

Hope that makes sense!

Thanks!
Christa

Function strip_url(URL As String) As String

    Dim domain_name As String
    Dim N As Long
    Dim M As Long
    Dim S As String
    Dim URL_split As Variant

    N = InStr(1, URL, "//") + 1
    If N > 2 Then
        URL = Right(URL, Len(URL) - N)
    End If
    
    
   
   URL_split = Split(URL, "/")
   
   For i = 0 To UBound(URL_split)
   
   'Cut the www
    N = InStr(1, URL_split(i), "www")
    If N > 0 Then
        URL_split(i) = Right(URL_split(i), Len(URL_split(i)) - 4)
    End If
     
    N = InStr(1, URL_split(i), ".")
    Q = InStr(1, URL_split(i), "?")
 
    Do While (Q <> 0 Or N <> 0)
    'Cut the .com or .org etc
        If (Q = 0) Then
            'Cut the whole right
            URL_split(i) = Left(URL_split(i), N - 1)
        ElseIf (N = 0 And Q <> 0) Then
            URL_split(i) = Left(URL_split(i), Q - 1)
        ElseIf Not (Q < N And Q <> 0) Then
            '? not before the . Cut out anything in between
            URL_split(i) = Left(URL_split(i), N - 1) & Right(URL_split(i), (Len(URL_split(i)) - Q + 1))
        End If
        
              
        N = InStr(1, URL_split(i), ".")
        Q = InStr(1, URL_split(i), "?")
        
    Loop
    
    Next
    
    'String together the URL
    URL = ""
    For i = 0 To UBound(URL_split)
        URL = URL & URL_split(i)
    Next
    
    strip_url = URL
End Function

Open in new window

christamccAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

krishnakrkcCommented:
Hi,

Try something like..

Function STRIPURL(URL As String) As String
    URL = LCase$(URL)
    URL = Replace(Replace(URL, "http://", ""), "www.", "")
    STRIPURL = Split(URL, ".")(0)
End Function

Sub Strip_All_URL(ByRef InputRange As Range, ByRef OutputRange As Range)
   
    Dim k, i As Long
    k = InputRange.Value
   
    If IsArray(k) Then
        For i = 1 To UBound(k, 1)
            If Len(k(i, 1)) Then
                k(i, 1) = STRIPURL(CStr(k(i, 1)))
            End If
        Next
        OutputRange.Resize(UBound(k, 1)) = k
    Else
        OutputRange = STRIPURL(CStr(k))
    End If
   
End Sub

and call the macro like..

Strip_All_URL range("a1:a10000"),range("b1")

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
christamccAuthor Commented:
Thanks krishnakrkc!  

The Sub ALL is great, just what I needed... I am allowed to ask you to expand on the Macro running?  I was trying to remember how to run a Macro with parameters, it's been a little while!

I made a simple form that accept the ranges from text fields and button to call the sub from with in the CommandButton1_Click() sub, but is there a simpler way?  (I'm the only one using this Workbook, so simplicity is the best solution, it doesn't have to look pretty. ;)  )

(Let me know if I should close this question and open another one for the macro question!)
0
krishnakrkcCommented:
Hi,

Insert a shape and assign this macro 'Test'

Sub Test()
    Strip_All_URL Range("a1:a10000"), Range("b1")
End Sub


You could even call this in the CommandButton1_Click event.


Note: adjust the range.

HTH

0
christamccAuthor Commented:
thanks!  I ran it and it is good, except it gets stuck at empty fields, that I fixed.  And I think it gets stuck when there is a % in the field.  Would that make sense?  I suppose I should just strip them before the loop?

Sub Strip_All_URL(ByRef InputRange As Range, ByRef OutputRange As Range)
    
    Dim k, i As Long
    k = InputRange.Value
 
 On Error GoTo errMyErrorHandler
    If IsArray(k) Then
        For i = 1 To UBound(k, 1)
            If Len(k(i, 1)) Then
                k(i, 1) = strip_url(CStr(k(i, 1)))
            Else
               k(i, 1) = " "  'fix empty fields
            End If
        Next
        OutputRange.Resize(UBound(k, 1)) = k
    Else
        OutputRange = strip_url(CStr(k))
    End If

Exit Sub

errMyErrorHandler:
  MsgBox Err.Description, _
    vbExclamation + vbOKCancel, _
    "Error: " & CStr(Err.Number)
  Err.Clear

End Sub
    

Open in new window

0
krishnakrkcCommented:
Hi,

That error might be in the UDF.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.