VBA Remove a part of the code and put him into a separate procedure

Question

I have absolutely no idea how to create separate subs/functions to shorten the code. I am referring to those subs(something as integer, etc)

Below we have this code that resides in my core module

Set els = IE.Document.getelementsbytagname("a")
    For Each el In els
        If Trim(el.innertext) = "Documents" Then
            colDocLinks.Add el.href
        End If
    Next el

    For Each XML_link In colDocLinks
        LoadPage IE, CStr(XML_link)
        For Each el In IE.Document.getelementsbytagname("a")
            If el.href Like "*[0-9].xml" Then
                With Worksheets("CONTROL_ROOM").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                    .NumberFormat = "@"
                    .Value = Ticker
                    .Offset(0, 1).Value = el.href
                End With
                Debug.Print el.innertext, el.href
                colXMLPaths.Add el.href
            End If
        Next el
    Next XML_link

I really need to shorten my code. How could i create a separate sub or function instead of having this chunk of code into my main module?

Books offer over-simplistic examples and have not been any help to me in real situations like this one. Do i need to make declarations such as Dim els inside the separate Sub or Function? Thank you for your patience in advance.

And most importantly no-matter how much time i look to these examples i cannot figure out which variables i put in here:

(Private) Sub / (Private) Function ( variables ?)

+++Any good examples/links will help.

You are free to never declare any variables (or to declare only some) but that's the road to buggy code. `Option Explicit` should be at the top of every module. If there are variables which you only use in a specific Sub or Function, then that's where they should be declared. You do not need to declare any variables passed in as parameters to your Sub/Function. Typically you use a function if you want to return a variable, and a Sub if you do not. — Tim Williams, Jun 03 '14 at 20:06
Try this: http://www.homeandlearn.org/index.html this should be a great start for you to learn the language. — Ben Black, Jun 03 '14 at 20:08
Thanks Guys (@TimWilliams this is your code that i found in an OS answer) how many where the odds of that? — Codo, Jun 03 '14 at 20:11
@TimWilliams http://stackoverflow.com/questions/21891468/vba-procedure-outputs-a-varying-number-of-string-variables-to-be-declared-and-re that's the one!!! — Codo, Jun 03 '14 at 20:13
Odds are quite high actually ;-) I thought those lines looked familiar. — Tim Williams, Jun 03 '14 at 20:17

score 1 · Accepted Answer · answered Jun 03 '14 at 21:11

Create a subroutine anytime you want to be able to call a block of code to do something, without returning any kind of value to the code that called it:

Sub MainCode()
Dim myString as String

...'all your main code

Call OtherSub(myString)

...'all your main code
End Sub

Sub OtherSub(theString as String)
'Do Something with the string theString
End Sub

Create a function when you want to return something:

Sub MainCode()

 Dim myString as String, newString as String

...'all your main code

NewString = OtherSub(myString)

...'all your main code

End Sub

Function ManipulateString(theString as String)

'Do Something with the string theString

 ManipulateString = theString & ...

End Function

At the end of the function, to return the new value, simply set the function name equal to whatever you are passing back.

Hope that helps.

Thank you man i can make my own Functions and SubRoutines now~ — Codo, Jun 04 '14 at 22:32

VBA Remove a part of the code and put him into a separate procedure

1 Answers1