Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
writing a formula in Excel spreadsheet to track how long data has | Excel Worksheet Functions | |||
Writing a macro that will exit a spreadsheet | New Users to Excel | |||
Writing bitmap to Excel Spreadsheet | Excel Programming | |||
Writing to a excel spreadsheet is slow. | Excel Programming | |||
Writing a macro so that when saving a spreadsheet the data cannot be changed | Excel Programming |