This is the reason why the power of Excel when combined with VBA is almost limitless. When we can’t found any Excel function that suite our need, we can built it easily using Excel VBA.
If Excel macro or SUB procedure is easily created using Record Macro button, and usually build to simplify a complex process to became one simple push button or call, then an Excel function build to return a value from any kind of calculation and can be use and invoke as a formula.
For example, we can create a simple Excel function to get the last date of the month with date as parameter.
Public Function LASTDATEOFMONTH(ByVal TheDate As Variant) As Date
Dim nLastDay As Integer
nLastDay = DatePart("d", DateAdd("d", -1, DateAdd("m", 1, DateAdd("d", _
-DatePart("d", TheDate) + 1, TheDate))))
LASTDATEOFMONTH = DateSerial(Year(TheDate), Month(TheDate), nLastDay)
End Function
See the following result.
The result of the formula shows us that the last date of this month is June 30, 2008.
Using Excel function, we can create any kind of specific formula that’s not available in Excel and need to be used for several time on our Excel project.
Hi there,
Just a question on VBA functions, say if I created a function for one project, I can’t use the same function on the other project. (unless I copy and paste the code to the module under that project. I’ve noticed that there is a PERSONAL.xls project always in VBA no matter what excel project I’m on. but I tried to copy the function in there it still doesn’t work.
Could you please help?
thank you very much!
Robert
hi robert, please make sure that you use public keyword in the module, like in the example above i use “public function”.
if we state “public” in front of function or sub, that this method can be called from anywhere in the same project.
hope this will help you.
Hi, I wonder if you can help me please, I am having a problem with a user form in which I want to calculate a ‘Private Drawing’ perecentage deduction from a Full purchase amount to give a Taxable amount. The user would choose one of a number of perecentages from a list having entered the full price, then click a cmd button for a sub that should take price and deduct (the price times say .1 (10%)), instead it just zeroes the full price, I want the user to see this figure before clicking OK and sending all the other purchase details into the sheet. Sorry if this is long-winded. Thanks Duncan
Hi,
I want to get the row value(cud be groups of wrds) into new sheet.
Similarly, i want to get this kind of information from different worksheets into one consolidate sheet.
Here, i dunno the number of rows in particular worksheet. But i want all row information.
Please help me in this…
Please leave reply to sravandotcom@gmail.com or sravankumar.nallana@cognizant.com;
Please call me at 9025638147.
thanks
Pingback: cheating wife website
Pingback: cheap christian dior shoes
Pingback: Breville BJE820XL Dual Disc Juicer
Pingback: organic seo company
Pingback: reverse mortgage loan
Pingback: debt consolidation company
Pingback: feuerlandkirsch.de/alles-uber-routenplaner
Pingback: Anonymous
Pingback: cul
Pingback: ares free file downloads
Pingback: ear popping
Pingback: debt collection rate
Pingback: collection agency rates
Pingback: latest casino bonuses
Pingback: Guided Christian Meditation
Pingback: erotische masage
Pingback: vimax
Pingback: wedding photographer calgary
Pingback: how to enlarge
Pingback: hier
Pingback: Wedding Photography
Pingback: ???????????
Pingback: have an affair
Pingback: Cheap Fitflop Toning Shoe
Pingback: cushion cut egagement ring
Pingback: wie kann ich schnell abnehmen
Pingback: madison street beauty
Pingback: Sexlijnen
Pingback: Gaming Mouse Reviews
Pingback: ?????????? ??????????
Pingback: Apps For Kids
Pingback: more
Pingback: devis assurance auto
Pingback: hobby lobby coupon
Pingback: prets personnels
Pingback: credit monitoring
Pingback: tricksterzz
Pingback: kamagra
Pingback: ??? ???????
Pingback: job hunter
Pingback: hobby lobby coupon
Pingback: chota bheem games
Pingback: Justin Bieber Online Games
Pingback: Amazon promotional code
Pingback: http://jawadsatti.com/
Pingback: chhota bheem games