Return a format by VBA function

[Titoun]

Hello, (I'm just started with VBA..)

I am trying to copy the format (font, interior and value)
of a cell to another by a VBA function.
The below Sub is working well but I need it under
function. Did you have any idea on how correct the below
function or did you have another one in stock.

Sub Copy_Format_And_Value_Of_One_Cell_In_Active_Cell()
Rng = InputBox("Adresse of the cell", "Copy Formats")
Range(Rng).Copy
ActiveCell.PasteSpecial Paste:=xlFormats
ActiveCell.Value = Range(Rng)
Application.CutCopyMode = False
End Sub


Function Cop_Form(InRange As Range)
For Each cellule In InRange
Range(cellule).Copy
ActiveCell.PasteSpecial Paste:=xlFormats
ActiveCell.Value = Range(cellule)
Application.CutCopyMode = False
Next cellule
End Function

Thanks in advance for your great help.
Titoun

 

[Harlan Grove]

...
...

I am trying to copy the format (font, interior and value)
of a cell to another by a VBA function.
The below Sub is working well but I need it under
function. . . . ...
Function Cop_Form(InRange As Range)
For Each cellule In InRange
Range(cellule).Copy
ActiveCell.PasteSpecial Paste:=xlFormats
ActiveCell.Value = Range(cellule)
Application.CutCopyMode = False
Next cellule
End Function

Since the function above just returns Empty (since it defaults to return type
Variant, and so its value then defaults to Empty), it doesn't need to be a
function if it were called from another VBA procedure. I suspect you want to
call it as a user-defined function from a cell formula. If so, you're out of
luck - udfs can't alter the Excel environment (directly), so they can't make or
change entries in other cells, change formats of other cells, open or close
other workbooks, etc. UDFs are *only* allowed to return values to the calling
cell.

You can accomplish what you want with Calculate or SheetCalculate event
handlers, which are macros that run automatically after recalculation.