Function vs. Procedure

dave8

Active Member
Joined
Jul 8, 2007
Messages
264
Can someone tell me the difference between a Function and Procedure? From what I understand is that a Function returns a value. Can a Procedure return a value? How?
 

SidBord

Active Member
Joined
Aug 23, 2004
Messages
346
The primary effect of a function is to behave like a variable, except the function must do some processing whereas a variable can’t do any processing. One other MAJOR difference is that a macro function MUST NOT do anything that changes anything on a worksheet!! A function can otherwise do just about any kind of processing that a procedure can. It MUST however execute a statement that returns a value. For example, if you write a function named “GetThisSheetName”, somewhere in the code you MUST have a statement something like this:

GetThisSheetName = “Name of Sheet”
and that statement must be executed before exiting the function. Then, if used in a worksheet formula, you might write something like:
= Call GetThisSheetName()
Note that you MUST use parentheses following a function name that’s referenced in a formula. When used in a macro, the function name does not require the parens and you don’t need the “Call”. I suggest you look at help for the rules about arguments.

A procedure can return one or more values by using arguments. Normally arguments are used to pass information “to” a macro procedure, but you can use an argument to return a value ”from” the procedure to the caller if convenient.

One other thing to consider is results of an error that occurs in a function. Since a function MUST always return a value, you should provide a returned value that tells the calling routine a disaster has occurred. If your function normally returns a positive integer as a value, then you could return -1 if an error occurs.
HTH, Sid
 

Forum statistics

Threads
1,084,752
Messages
5,379,650
Members
401,619
Latest member
manpais

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top