VBA UDF Writing data to spreadsheet
 

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

--------------
thanks

Macroman

VBA UDF Writing data to spreadsheet
 

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" wrote in message
...
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

--------------
thanks

Macroman

VBA UDF Writing data to spreadsheet
 

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


"Niek Otten" wrote in message
...
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" wrote in message
...
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

--------------
thanks

Macroman

VBA UDF Writing data to spreadsheet
 

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

--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2000 SP-3]

VBA UDF Writing data to spreadsheet
 

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)


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


"Niek Otten" wrote in message
...
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" wrote in message
...
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

--------------
thanks

Macroman