• 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...

  • 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

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:
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
 
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
 
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
 

Marc L

Excel Ninja
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 !
 

SirJB7

Excel Rōnin
To close this sample, an expert combines an external Windows object
with Excel inner features so its process only needs around one second !​
@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!
 
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
 
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
 

Marc L

Excel Ninja
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.
 
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
 
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
 
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:
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
 

Debaser

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

Code:
If InRange.Cells.countlarge = 1 Then
 
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
 
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:
Sub Select_First_last()

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

End Sub
 
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
 

SirJB7

Excel Rōnin
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:
function Unga (param1 as type1, param2 as type2) as type
    ...
    do something
    ...
    Unga = Value
End Function
Equivalent subroutine:
Code:
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!
 
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:

Debaser

Active Member
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.
 
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
 
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
 
Top