VBA UDF Writing data to spreadsheet

[Macroman]

I am writing a simple function which asks the user for a range of cells.

I would like the range of cells analysed and replaced with new values but I
am unable to write back to the spreadsheet.
I can create a Sub (macro) to do this but I prefer a function then I can add
the function to my UDF AddIns.
The code as follows
-----------
Function Grab(Grab_Range As Range)

intCellLoc = ActiveCell.Address
varSheetName = ActiveSheet.Name
intRows = Grab_Range.Rows.Count
intColumns = Grab_Range.Columns.Count

intRepRow = Grab_Range.Cells(1, 1).Row
intRepCol = Grab_Range.Cells(1, 1).Column

If intColumns <> 1 Then ErrMsg = "#Column Err#": GoTo Finished

########It is this bit I cant get working#############
For i = 1 To intRows
Sheets(varSheetName).Cells(intRepRow+(i-1) , intRepCol).Value = "Test"
Next
######## Any Ideas ? ############
Finished:
If ErrMsg = "" Then ErrMsg = "Finished"
Grab = ErrMsg
End Function

 

[Niek Otten]

Hi Macroman,

There is no way you can change anything in your spreadsheet (values,
formats, etc) from within a function.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Macroman]

Thanks , is it therefore possible then to call a subroutine from within a
funcion and have the subroutine do the work.

 

[Thomas Ramel]

Grüezi Macroman

Macroman schrieb am 17.04.2005

Thanks , is it therefore possible then to call a subroutine from within a
funcion and have the subroutine do the work.

No, this is disabled as well when called from a cell in the worksheet.
A function is only allowed to deliver a value to the cell where its
called from.

But you could use the Worksheet_Change()-Event to trigger a subroutine
with your code.


Mit freundlichen Grüssen
Thomas Ramel

 

[Bob Phillips]

No that is effectively the same thing. Try Worksheet_Calculate

Private Sub Worksheet_Calculate()
If Me.Range("A1").Value > 10 Then
'do your stuff
End If
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)