Hi,
I have an error in my vba application.
It occours only in excel 2003, but not in 2000.
Its a:
"Application-defined or object-defined error"
Error code 1004
In the follwoing method the error occurs:
Public Function SaveResultsToExcelSheet(RangeOfValues As Range, ValuesArray() As Double) As Boolean
On Error GoTo Error:
SaveResultsToExcelSheet = True
Dim OutputSheet As Worksheet
Dim OutputCell As Range
Dim Counter As Long
Counter = 0
For Each OutputCell In RangeOfValues
OutputCell.value = ValuesArray(Counter)
Counter = Counter + 1
Next OutputCell
Exit Function
Error:
Err.Clear
SaveResultsToExcelSheet = False
End Function
The error occurs in the line "OutputCell.value = ValuesArray(Counter)".
It is not possible to assign a value to that range.
Could anyone help here
regards
can you help ?
jwraith
1.) Is the workbook protected
2.) Is that range locked
3.) Are you sure you're passing a valid range (Have you checked )
This code works fine for me. Please check these 3 items and then, if you're still having a problem, let me know exactly what error you're receiving.
quintinsreb
Hi,
You know what I think is happening. I had a look at your bug.xls and the problem is basically updating a cells value.
So I did this... I create a test sub that just output some value... no error
Public Sub Test()
ActiveSheet.Range("A1").Value = 10
End Sub
And then I placed the same line in SaveResultsToExcelSheet.. error
The only reason I can think that this might happen is this.... because your activating a cell and pressing return in the address bar to fire the function your essentially locking the sheet. Your pressing return on A3 from the address bar and when you do that you cannot modify any other cells values. Even from code it seems.
To test my theory I fired your macro from a command button and your code works fine. It's not working because of the address bar.
ZuliXP
Hi, this function is called via a macro.
The function is called via a toolbar in excel which calls a "simulate()" method, which does exactly this.
There are different modules in this application.
But calling it via this toolbar action, it throws the error.
could you give me the code to call the method via a button
I created a command button, but I dont know what to write into the script code of the button...
Thanks a lot !
rgds
Arun.kumar.sharma
Hi,
You write this in the command buttons onclick sub
Random_MultiVariate_NormalA(Range("C1:D2"),Range("F1:F2"),Range("H1:H2"))
Thats replicating the bugs.xls spreadsheet.
leclerc9
Hi,
thank you for your answer.
But that was not the reason.
Hm...its crazy...
I attached the excel sheet to my server, it would be wonderful if you could have a look at it :-))
http://www.css-limited.de/bug.xls
Its a simple random functionality.
The input fields are: Range C1:D2 and Range F1:F2
The output cells are: Range H1:H2
You can run the functionality when you point to cell "A3" and then go into the function row above and press enter.
To debug, go into the VBA script and make a breakpoint in this method:
"Public Function SaveResultsToExcelSheet(Values As Range, ValuesArray() As Double) As Boolean"
...
HERE --> For Each OutputCell In Values <<-- HERE !!!
OutputCell.Value = ValuesArray(Counter)
Counter = Counter + 1
...
Error:
Err.Clear <<-- Here the error is catched. See the details aof the error.
SaveResultsToExcelSheet = False
End Function
So, in Excel2000 (I am using 2003 and I need to run it on 2003) there is NO error.
If it would work correctly, it would show randon figures in the H1:H2 range as a result. But it shows nothing, since there is the error.
It would be really great , if you can help me !!!
regards
Tommy