• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

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

0
christamcc
Asked:
christamcc
  • 3
  • 2
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now