Excel VBA Basics #11 Create your Own Custom Functions with or without Arguments

How to make a function using VBA. In this lesson, we make 2 functions from scratch, =Lastrow() and =LastrowC().

Lastrow function, when typed into any cell, gives the row number of the bottom most cell in column one.

LastrowC function allows the user to click on any cell for its argument and it will find the lastrow in whatever column the selection was in.

Fantastic Developer Tools:

[danstrong]

That's a great idea! Use a custom function to get the last row on the sheet that you use often!! Brilliant! Dan

[couponcharlie]

I want more!! These were great. Thanks for dedicating your time!

[ExcelVbaIsFun]

Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan

[ExcelVbaIsFun]

Sure, Harish. Can you copy/paste the code you used? Also, sometimes you have to be careful which column you're using. If you have that directed to something other than col 1 and then you're meaning for it to check col 1, it sometimes will give you trouble.

Here's the basic format:

lrow=sheets("Sheet1").cells(rows.count, 1).end(xlup).row

Talk atcha soon,
Dan

[Nino]

Great work with the videos!

Unless I'm doing something wrong I noticed this doesn't work if your data is structured as a table.

[ExcelVbaIsFun]

More are on the way, sir! Thanks for your time in watching and thanks for taking the time to make a comment. Please "Like" any videos you think are cool! Lemme know if you have any questions, sir.

[excelsocallmaster]

Hi dan, excellent video just one point that I'm sure you know anyway use Application.Volatile at the first of your UDF will update when workbook is recalculated

[ExcelVbaIsFun]

Carlos, after much avail I figured it out. Remove your function from the Sheet and into a MODULE. Apparently excel doesn't like Functions in a sheet or ThisWorkbook object, but rather in a Module. It's kinda picky. Thanks!

[MohammedKalache]

hey there,

thanks for the vid,

i have one question, can we have a help bar when we are using the formula, like the one in Sum(Range 1, Range 2, ... ) ?

[scotolivera]

Thanks for the video . But about the tool tip any ideas ?

[mmatss]

Thanks for another great video! Two questions: how is the syntax for finding LastrowC here .End(xlUp).Row instead of .End(xlDown).Row? Also, in the end of the video you added more dates to the third column and the cell with the custom function did not update until you ran the custom function again manually. Did you have the automatic calculation disabled from application options, or why did it not update the cell value automatically?

[anveshkoripalli]

Thank you so much for your videos, it is very easy to learn,

I have one issue : I have a data where i need to use that data to submit a ticket, . I managed everything,

Open the explorer

put the required data over there

and also submit, but before submitting i need to attach a file
code is able to copy the file name from excel, click the browse button on explorer, but it is unable to paste and click open on the dailouge box, i am doing it manually.

can u help fix that ? (Im using IE automation as i do not html to do it on firefox)

[TheScottek]

I used this code-
Function Lrow()

Lrow=Sheets("Sheet2").cells(Rows.count, 1).end(xlup).row

End function

I have my data in Sheet2..

[ExcelVbaIsFun]

Hmmm. What do you mean Doug, about refreshing? They typically recalculate just as often as built in ones. What problems are you having?

PS I had a wonderful professor at Rhema Bible Training Center in Tulsa OK named Doug Jones, great name!

[Odysseus]

Nice example and well presented :)
I have just one question:
Why isn't the lastrowC function autoupdated after you change the lengt of column C - and is there any way to make a function that actually updates automatically when changing your sheet data?
Kind regards!

[ExcelVbaIsFun]

I'm not familiar with that approach, do you have a sample of the code or a sample wb I could look at? Thanks! Daniel

[JanBolhuis]

Hello Dan, Thanks for sharing your knowledge.

Is it possible that is will also work without define selectedcells as a range? Here is how it works with me. Or are there any disadvantages if you don't use is as a range?

Function LastRowC(selectedcell)

sc = selectedcell.Column

LastRowC = ThisWorkbook.Sheets("Functie").Cells(Rows.Count, sc).End(xlUp).Row

End Function

[mr.kamalsah]

Hello Sir,
I have one queries of, I have created some of code for my own function but, it doesn't work if I one new file... Is it like that of, new created function will only work in file you saved it...????

Waiting for your response sir...

[vibhavarivyas]

Hi.

I am trying to create a same vba function and it is not working

When I go to execute the function in my spreadsheet it is recognized, but It returns "#NAME?"

When I hover my mouse over the text I get "This formula contains unrecognized text"

When I hover my mouse over the stop sign looking button thing it says "Invalid Name Error"

When I try same function in another sheet it works perfectly fine. Even it works in the another cell in the same worksheet. I also tried clear formats for the cells in which this gives above error.

Can you pls shed some light on this?

[uncommonsense]

I am wondering if there is a way to get around having to double click on the cell for the value to change when its inputs have changed? I ask because I am making a function that I would like to apply solver to, but it wont work if the cell value does not update with each iteration. Any suggestions?