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 convert Function To Sub

Discussion in 'VBA Macros' started by Chirag R Raval, Jun 8, 2017.

  1. Chirag R Raval

    Chirag R Raval Member

    Messages:
    173
    Dear Sir,

    I got a function for search used range of Last Cell of data to conver it to sub for
    how to convert it to Sub Procedures??


    Code (vb):
    Public Function GetLastCell(InRange As range, SearchOrder As XlSearchOrder, _
        Optional sheet As Worksheet, Optional ProhibitEmptyFormula As Boolean = False) As range
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' GetLastCell
    ' By Chip Pearson, chip@cpearson.com, www.cpearson.com
    '
    ' This returns the last used cell in a worksheet or range. If InRange
    ' is a single cell, the last cell of the entire worksheet if found. If
    ' InRange contains two or more cells, the last cell in that range is
    ' returned.
    ' If SearchOrder is xlByRows (= 1), the last cell is the last
    ' (right-most) non-blank cell on the last row of data in the
    ' worksheet's UsedRange. If SearchOrder is xlByColumns
    ' (= 2), the last cell is the last (bottom-most) non-blank cell in the
    ' last (right-most) column of the worksheet's UsedRange. If SearchOrder
    ' is xlByColumns + xlByRows (= 3), the last cell is the intersection of
    ' the last row and the last column. Note that this cell may not contain
    ' any value.
    ' If SearchOrder is anything other than xlByRows, xlByColumns, or
    ' xlByRows+xlByColumns, an error 5 is raised.

    '
    ' Sheet is an optional specifier of which sheet to search. If omitted,
    ' the ActiveSheet is used.
    '
    ' ProhibitEmptyFormula indicates how to handle the case in which the
    ' last cell is a formula that evaluates to an empty string. If this setting
    ' is omitted for False, the last cell is allowed to be a formula that
    ' evaluates to an empty string. If this setting is True, the last cell
    ' must be either a static value or a formula that evaluates to a non-empty
    ' string. The default is False, allowing the last cell to be a formula
    ' that evaluates to an empty string.
    '''''''''''''''''''''''''
    ' Example:
    '      a  b  c
    '              d  e
    '      f  g
    '
    ' If SearchOrder is xlByRows, the last cell is 'g'. If SearchOrder is
    ' xlByColumns, the last cell is 'e'. If SearchOrder is xlByRows+xlByColumns,
    ' the last cell is the intersection of the row containing 'g' and the column
    ' containing 'e'. This cell has no value in this example.
    '
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim WS As Worksheet
    Dim R As range
    Dim LastCell As range
    Dim LastR As range
    Dim LastC As range
    Dim SearchRange As range
    Dim LookIn As XlFindLookIn
    Dim RR As range


    If sheet Is Nothing Then
        Set WS = ActiveSheet
    Else
        Set WS = sheet
    End If

    If ProhibitEmptyFormula = False Then
        LookIn = xlFormulas
    Else
        LookIn = xlValues
    End If

    Select Case SearchOrder
        Case XlSearchOrder.xlByColumns, XlSearchOrder.xlByRows, _
                XlSearchOrder.xlByColumns + XlSearchOrder.xlByRows
            ' OK
       Case Else
            Err.Raise 5
            Exit Function
    End Select

    With WS
        If InRange.Cells.count = 1 Then
            Set RR = .UsedRange
        Else
          Set RR = InRange
        End If
        'Set R = RR(RR.Cells.Count)
       Set R = RR(1, 1)
       
        If SearchOrder = xlByColumns Then
            Set LastCell = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
                    LookAt:=xlPart, SearchOrder:=xlByColumns, _
                    searchdirection:=xlPrevious, MatchCase:=False)
        ElseIf SearchOrder = xlByRows Then
            Set LastCell = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, _
                    searchdirection:=xlPrevious, MatchCase:=False)
        ElseIf SearchOrder = xlByColumns + xlByRows Then
            Set LastC = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
                    LookAt:=xlPart, SearchOrder:=xlByColumns, _
                    searchdirection:=xlPrevious, MatchCase:=False)
            Set LastR = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, _
                    searchdirection:=xlPrevious, MatchCase:=False)
            Set LastCell = Application.Intersect(LastR.EntireRow, LastC.EntireColumn)
        Else
            Err.Raise 5
            Exit Function
        End If
    End With

    Set GetLastCell = LastCell

    End Function

    Public Function GetFirstCell(InRange As range, SearchOrder As XlSearchOrder, _
        Optional sheet As Worksheet, _
        Optional ProhibitEmptyFormula As Boolean = False) As range
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' GetFirstCell
    ' This returns the first cell in a range or worksheet.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Dim WS As Worksheet
    Dim R As range
    Dim C As range
    Dim LookIn As XlFindLookIn
    Dim RR As range

    Select Case SearchOrder
        Case XlSearchOrder.xlByColumns, XlSearchOrder.xlByRows, _
                XlSearchOrder.xlByColumns + XlSearchOrder.xlByRows
            ' OK
       Case Else
            Err.Raise 5
            Exit Function
    End Select

    If ProhibitEmptyFormula = False Then
        LookIn = xlFormulas
    Else
        LookIn = xlValues
    End If

    If sheet Is Nothing Then
        Set WS = ActiveSheet
    Else
        Set WS = sheet
    End If

    With WS
        If InRange.Cells.count = 1 Then
            Set RR = .UsedRange
        Else
            Set RR = InRange
        End If
       
        If RR(1, 1).Formula <> vbNullString Then
            Set GetFirstCell = RR(1, 1)
            Exit Function
        End If
       
       
        If SearchOrder = xlByColumns Then
            Set R = RR.Find(what:="*", after:=RR.Cells(1, 1), _
                    LookIn:=LookIn, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                    searchdirection:=xlNext, MatchCase:=False)
        ElseIf SearchOrder = xlByRows Then
            Set R = RR.Find(what:="*", after:=RR.Cells(1, 1), _
                    LookIn:=LookIn, LookAt:=xlPart, SearchOrder:=xlByRows, _
                    searchdirection:=xlNext, MatchCase:=False)
        ElseIf SearchOrder = xlByRows + xlByColumns Then
            Set R = RR.Cells(1, 1)
        Else
            Err.Raise 5
            Exit Function
        End If
    End With

    Set GetFirstCell = R

    End Function
    hope your co-operation for understand things ..
    for further work..

    Regards,
    Chirag
    for
  2. Chirag R Raval

    Chirag R Raval Member

    Messages:
    173
    Dear All,

    How to successfully use this function....?

    (1) As function
    (2) as sub.

    Regards,
    Chirag Raval
  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    14,780
  4. Chirag R Raval

    Chirag R Raval Member

    Messages:
    173
    Dear Sir,

    Thanks for give link for study the matter..
    I already jump on links..copy paste-that matter in word, I will be print it...& try to understand VBA- Sub -Func..& try to modify this code as a Sub...&

    I Will Be Back..

    Till then...

    Thanks Again for give links to proper guideline for self study & apply that to modify this code as requirement...

    Regards,
    Chirag Raval
  5. Debaser

    Debaser Active Member

    Messages:
    342
    Why would you want to convert it to a Sub, and what do you expect it to do?
    Marc L likes this.
  6. Chirag R Raval

    Chirag R Raval Member

    Messages:
    173
    Dear Sir,

    Really I want to say I am new in VBA ...
    I just learning all the things....But I really amaze with the power of VBA..
    I search , Print...Read at night & fight (try) to understand VBA..

    I first time face at those from reading...Objects...Its Properties...Methods...Statement...Loops...

    aah ...its made me amaze all the time..when just something reading about...& realize the things....I think just work in excel spread sheets is not really excel..
    real thing is VBA...


    when My Family sleep..i study all the things ..some practice at my office where I employee.. since 10 years ...as a simple clerk...but...earlier I face VBA & its power... I apply codes on my reports...& recently apply VBA Array code for Vlookup & just generate results in 3 columns in seconds...which take huge time before I face VBA ...& many mores...I have many subs ...I use them in my works...& as apply new...hunger for more macros raised...& also raised learning hunger about that...

    Because I don't now how to use this function?
    if we want to run this function Through sub then how can we run ??
    i want to apply this sub for decide my range & just
    try to convert functions to sub to use & call in another subs..

    my story have no end...(please read as In VBA)...

    if someone hint for just converting.. then I can convert all possible functions in Subs..

    hope you understand my condition..

    Regards,
    Chirag Raval
  7. Marc L

    Marc L Excel Ninja

    Messages:
    3,043
    Hi !

    Far better is to first think Excel before VBA !
    As the first rule, it can avoid useless VBA code and as
    Excel inner features are often faster than a general / standard VBA code …

    A recent example from another forum : a guy proud of his VBA code
    deleting rows between 300k source rows under condition with a loop
    but complaining it was a bit long, ten minutes.

    Doing the same process manually just using Excel basics
    (formula, sort, clear) lasts less than a minute ‼
    After automatization so without any loop it needs around 30 seconds !

    So even a VBA beginner just well knowing how Excel works can achieved
    a better process than any developer with a classic code …

    To close this sample, an expert combines an external Windows object
    with Excel inner features so its process only needs around one second !
    Chirag R Raval and shahin like this.
  8. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,688

    @SirJB7
    Hi, myself!
    That's why I do need to find an expert! 9min 59sec more for each 10min time lapse to drink a Carlsberg! And let @Marc L join me, of course!
    Regards!
    Marc L likes this.
  9. Chirag R Raval

    Chirag R Raval Member

    Messages:
    173
    Dear Sir,

    You are absolutely right...but....
    in Excel..
    when your receiving various Same data Structure Daily...till last 5 years & on that basis
    You generate Various same type of various reports ..Daily... till last many years...
    Some time Same Whole reports Require Regenerate Daily....

    when some someone use excel till many years ...he should also go to vba ...


    Any one tired... if he take advantage of VBA...then he is on the right path
    & that's you all already believe...


    may be we are goes out side of subject....

    Now I want to get again I want to know about this function..
    How to use this function...
    Function take arguments...(some optional some required)
    if we fulfil argument's required portion we can generate result ..

    some where I read if you Put "Sub" instead of "Function" against function name..but.. I try...its... not always true..

    I also know function's area is limited..&

    how to fulfil its arguments if we want to convert it in Sub..

    Just Want to know Some Basic rules of Convert Function to Sub..
    so we can apply & go through at any function..

    Regards,

    Chirag Raval
  10. Chirag R Raval

    Chirag R Raval Member

    Messages:
    173
    Dear Sirs,

    Till now I successfully Complete many repeated task in my work ..
    from help of "Magical Marcos .." & get a more proper time for next pending work..which pending to complete..

    I also try to Call this function in macros (Sub)
    but I don't know how to fulfil its arguments..

    actually ...till now (since get this function from net) I can not use this function in any way to just check it...even any way...

    can I check this function..after convert this function as a sub?

    if this standard method available.... I can check every functions
    separately to modify basic function which use in sub..& also can apply that modified functions in any sub...

    hope your co-operations..

    Regards,
    Chirag Raval
  11. Marc L

    Marc L Excel Ninja

    Messages:
    3,043
    So you must start to clearly understand the difference
    between a function and a sub (procedure) …

    Then you may define what is variable and what does not change
    so the variable could be a parameter of a function or a procedure.
  12. Chirag R Raval

    Chirag R Raval Member

    Messages:
    173
    Dear Sir,

    thank for give your valuable tip..& I already started to print , study & try to understand this things..

    but till then.....can you hint how to get result of above (Message No 102) function. ( how to use above functions)

    Regards,

    Chirag Raval
  13. Chirag R Raval

    Chirag R Raval Member

    Messages:
    173
    Dear Sir,

    There are now result if I try to run below macro in which I use
    above 2 functions..

    created 2 variables (As Range ) catch result of Each above functions ....

    & I already use that Variables in sub to
    just select range as described above 2 functions..

    Sub Select_First_last()

    Dim A As range
    Dim B As range

    Set A = getfirstcell
    Set B = getlastcell

    ActiveSheet.range(A, B).Select

    End Sub

    is am i need to change in my code? i am near to get result??

    please help..

    Regards,

    Chirag Raval
  14. Chirag R Raval

    Chirag R Raval Member

    Messages:
    173
    Dear Sir,

    Modify as rectification ...
    modified code by adding arguments to work this functions

    BUT........

    RUN-TIME ERROR: "6"
    OVERFLOW


    RAISED ON BELOW

    With WS
    If InRange.Cells.count = 1 Then



    Code (vb):


    Sub Select_First_last()

    Dim A As range
    Dim B As range


    Set A = getfirstcell(Cells, xlByColumns, , False)
    Set B = getlastcell(Cells, xlByColumns, , False)


    ActiveSheet.range(getfirstcell(Cells, xlByColumns, , False), getlastcell(Cells, xlByColumns, , False)).Select


    End Sub

     
    Please help...I am very near to see this is work..

    Regards

    Chirag Raval
  15. Debaser

    Debaser Active Member

    Messages:
    342
    If you pass all the cells in a worksheet, you need to use CountLarge in Excel 2007 or later:

    Code (vb):
    If InRange.Cells.countlarge = 1 Then
  16. Chirag R Raval

    Chirag R Raval Member

    Messages:
    173
    Dear sir, Debaser

    Its ...Work ...Great...after modify as per your suggestion ..Many Thanks..

    Actually I fulfil all arguments by just my inspirations of Subconscious ..
    which always in hunger for reading about Excel.. VBA ...but don't know about
    "you need to use CountLarge in Excel 2007 or later:"

    Many Thanks for Complete my 1st need of "how to use this function"

    Now I will try this same Function work as "Sub"...
    in that ...hope from your all ...to guide about little require twist
    in code..whenever I stuck somewhere in code..

    Again Thanks & machine Says after Recognised-initialised events.."I will be back..."

    Regards,

    Chirag Raval
  17. Chirag R Raval

    Chirag R Raval Member

    Messages:
    173
    Dear Sir,

    I trimmed above code...

    there are no need to declare Variable to catch result.
    If we want to testing purpose by Just select the range...


    if we want to use result in another process ... then we can declare variables to catch the results..& can use that variables in another process...

    Trimmed Code to run ...(Get Result from this function).... as just selection purpose.

    Code (vb):

    Sub Select_First_last()

    ActiveSheet.range(getfirstcell(Cells, xlByColumns, , False), getlastcell(Cells, xlByColumns, , False)).Select

    End Sub
     
  18. Marc L

    Marc L Excel Ninja

    Messages:
    3,043

    A function must start with Function statement in order to return any result,
    as you can read in VBA inner help …
    Chirag R Raval likes this.
  19. Chirag R Raval

    Chirag R Raval Member

    Messages:
    173
    Dear Sir,

    Thanks for your tip about Function...But ....if ...want to use function in another
    sub of same project...how to mention or call this function at there?

    Regards,

    Chirag Raval
  20. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,688
    Hi, Chirag R Raval!

    Despite your intended use of a function as a subroutine, which I won't analyse, a general rule might be this:
    Code (vb):
    function Unga (param1 as type1, param2 as type2) as type
        ...
        do something
        ...
        Unga = Value
    End Function
    Equivalent subroutine:
    Code (vb):
    sub Sunga (param1 as type1, param2 as type2, Unga as type)
        ...
        do something
        ...
        Unga = Value
    end sub
    And generally a function does:
    - make calculations
    - return values into parameters
    - return a value

    And a subroutine does:
    - make calculations
    - perform actions
    - return values into parameters

    Regards!
    Chirag R Raval likes this.
  21. Chirag R Raval

    Chirag R Raval Member

    Messages:
    173
    Dear Sir,

    thanks I can see...that ..

    If we want function Convert in Sub...there are 2 matters should notable

    In Function...
    Function's Statement's last result catching variable (Which stay after parenthesis & out side parenthesis)..at Function Statement's Line's end which will be use to catch calculation's result. Function's end there...there are no more any further process after that..in function. when last variable of function get result value...so if we want to use function's result in any more process...
    we should call that whole function in any subroutine...

    if we want to convert same function in Subroutine ...

    In Subroutine ...
    function's Same last variable now change position in Sub... it should be cover under parenthesis.... in Sub' Statement's Line's End... & there should not any single character after complete Sub's Statement line( in other word all variables declared in parenthesis in Sub Statement line)......at last .(Subroutine's end--Code's End) ..this last variable use to catch the result .. & we can use this result in any further process..(is mentioned in your above 2 part "And a subroutine does: -Section 2 " "- Perform actions". That's only possible through Subroutines only...

    And finally Function's area is limited & independent that created for just get one result... only...& it can be part of Subroutine (function can use in subroutine as a small single action among various process of subroutine)... so this way..function can be only single part of whole subroutine so Subroutine can perform next various action based on get result from sub's this single part-(this single function...)

    hope I am correctly catch the concept....

    Regards
    Chirag Raval
    Last edited: Jun 15, 2017
  22. Debaser

    Debaser Active Member

    Messages:
    342
    A function returns a value, a subroutine doesn't. That's really the only difference between them. It would rarely make sense to convert a function to a sub.
  23. Chirag R Raval

    Chirag R Raval Member

    Messages:
    173
    Dear Sir,

    Thanks for your above Final -Awakening- Rock Solid-Statement..

    Actually this thread .. start for just known possibility for How to Convert Function In sub..& really we clear here fundamental concept of Difference between Sub & Functions.. & use of them..

    if anyone know this fundamental (differential) concept between this 2 things
    can use both precisely in code..

    thanks again ..

    Regards,

    Chirag Raval
  24. Chirag R Raval

    Chirag R Raval Member

    Messages:
    173
    Dear All

    Many Thanks to Mr. Narayank991 for permit me to put below educational hint
    to deeply understand this concept..

    Here (on Below link) you can find some basic...pure educational ..really fundamental..written in very simple language for understand about Subroutine & functions..just 1 page ...with very simple understandable example

    though ...its written for language "Fortran" but if you study & revised its words
    I am sure you can understand what is Function? how to create it? & what is Subroutine..& how to use function in subroutine ...

    http://www.chem.ox.ac.uk/fortran/subprograms.html

    request to repeat reading & each instructions steps ..compare it in mind with example you can surely step by step understand & realise relation of Variables & argument...used in function & Subroutine. That really amaze you ..after you realise & catch the concept of how Function & subroutine..work in code..

    Thanks again...

    Regards,
    Chirag Raval

Share This Page