1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to write and call functions from a module

Discussion in 'VBA Macros' started by ThrottleWorks, Jan 10, 2017.

  1. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,689
    Hi,

    I hardly use functions in my coding. However I want to learn how to write functions and call from module.

    Can anyone please share links which will help me understanding functions. Thanks.
    Please see below example for your reference.


    Code (vb):

    Sub File_Select()
    Call FilePicker(Sheet1.TextBox1)
    End Sub

    Sub FilePicker(ByRef txt As Object)

      With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = False

      .Show

      If .SelectedItems.Count > 0 Then txt.Value = .SelectedItems(1)

      End With

    End Sub
     
    Last edited by a moderator: Jan 15, 2017
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,455
  3. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,455
    Here is a basic example

    Code (vb):
    Sub List1()

    Dim i As Integer
    Dim j As Integer

    'Make a  table of i & i^2
    For i = 1 To 10
      j = square(i)
      Debug.Print i; j

    Next i
    End Sub
    Code (vb):
    Sub List2()

    Dim i As Integer
    Dim j As Integer

    'Make a  table of i & i^2+10
    For i = 1 To 10
      j = square(i) + 10
      Debug.Print i; j

    Next i
    End Sub
     
    Code (vb):
    Function square(myNumb As Integer) As Integer
      square = myNumb * myNumb
    End Function
     
    You can see that the first module List1 makes a list of integers between 1 and 10 and the square of each number

    It uses a function Square() to work out the square

    Because square is a generic sort of function that you are likely to use in other applications, it can be used as in List1, where the numbers and the squares+10 are printed
    ThrottleWorks likes this.
  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,455
    For a sample where multiple values are sent to the function

    Code (vb):
    Sub List3()

    Dim i As Integer
    Dim j As Integer

    'Make a  table of i * j
    For i = 1 To 10
    for j = 1 to 10
      k = product(i,j)
      Debug.Print i; k

    Next i
    End Sub
    Code (vb):
    Code (vb):
    Function Product(myNumb1 As Integer, myNumb2 As Integer) As Integer
      Product = myNumb1 * myNumb2
    End Function
    ThrottleWorks likes this.
  5. Marc L

    Marc L Excel Ninja

    Messages:
    3,043
    ThrottleWorks likes this.
  6. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,689
    Hi @Hui sir and @Marc L sir, thanks a lot for the help.

    Am going through this and will revert with details.

    Have a nice day ahead. :)

    PS - Was trying to post yesterday only. Somehow it was not done. Just was as auto-complete while referring post.

Share This Page