Ask a new question

Excel VBA User Defined Functions

On the Excel Expert exam for 2007 there is an objective for creating a user defined function in VBA. I am frustrated in this area and when I go to VBA to learn how it all works it gets even more frustrating.

 

I don't have any clue of what I'm supposed to do with this coding. I'm stupid at programming. None of the book training or online training videos have not even come to close to getting it figured out in my mind.

 

When I create function in VBA where am I supposed to indent and add punctuation and all these other staements that don't make a hill of beans on sense.

 

You have Function and End Function then all of that in between. I just need to see several more examples of custom functions and please explain it to me line by line in this coding in terms that I can understand this.

 

I'm frustrated and dejected. I don't want to give up on my goals.

Answer
Answer

A user defined function can be incredibly complex, or very simple. Since you are just learming, here is a simple function. You would use this in a cell like so:
=WhereAmI()

 

'First declaration line shows what the function
'expects to be passed - in parens - and what
'it will return. In this example, it doesn't expect
'any passed argument and will return a string.

Function WhereAmI() As String

    Dim r As Range
    Set r = Application.Caller
   
'You return a value by assigning a value
'to the name of the function

    WhereAmI = "You are on sheet " & r.Parent.Name & _
               " in cell " & r.Address(False, False) & "."

End Function

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated October 5, 2021 Views 2,385 Applies to: