Hi, new here, pleased to meet you all.
I have only v little experience with VB and have done the coding as below but keep getting a Type Mismatch error 13. It would be great if someone could tell me why this is happening and point me in the direction to correct it.
The cells in XL sheet contain continuously updated DDLs (eg.=MT4|BID!EURUSD)
TIA
Please Login or Register to view this content.
Last edited by futurespec; 07-02-2012 at 09:45 AM.
Where does the error occur?
Everyone who confuses correlation and causation ends up dead.
Error occurs at....
In Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
at line:
If (Sheet1.Cells(4, x).Value = PairVals(0, j)) Then
What is in the cell referred to in sheet1 when the code runs? Error value, text, other?
it is a value (continuously updated) with 5 decimal places
No 'N/A' values are visible
Have tried changing type to variant, string etc all to no avail... same error
I'd be willing to bet that it actually doesn't have a number value when the code is running as I can't see a reason for a Type Mismatch otherwise. If you use
does the error persist?Please Login or Register to view this content.
Ok it stopped that mismatch but now the mismatch is at..
Private Sub Workbook_Open()
line...
PairVals(0, j) = Sheet1.Cells(4, x).Value ' each link stored in array
So does that give some idea for the error??
Appreciate your help with this
No the error has returned still?
Back to same linePlease Login or Register to view this content.
... If (Sheet1.Cells(4, x).Value = PairVals(0, j)) Then 'No change
This is hurting my head for something so simple (haha).
Have attached stripped down spreadsheet if that helps.
Perhaps:
Please Login or Register to view this content.
Ermm!!!
No.
Same error
I need a swear box 'cos this is going to be something so obvious (or not as the case may be) :-(
Let's try and narrow it down then. If you change the Redim line to just:
do you still get the error? If so, where?Please Login or Register to view this content.
Yes same error same place
OK, so it's not the conversion to a double that causes the problem. If you add this function in a normal module:
and then alter your code to:Please Login or Register to view this content.
does it resolve the issue?Please Login or Register to view this content.
Do appreciate your ongoing help.
No, it made no difference. (error in same place)
Please bear in mind that I have very little clue as to what I am doing (that should be easy to accept).
I am not convinced that it is the code. (like you say cannot see why it would be wrong).
When I put the new code in the 'Off' button on my spreadsheet was selected but it keeps coming up with the error ... how would it do that if the macro should not be running??
Does that make any sense or am I completely lost?
Where does the error occur with the new code?
Edit: just noticed you said it is in the same place! Now I am stumped since the function should override any errors. In the VBEditor, under Tools - Options, on the General tab, what is the Error Checking option set to?
Last edited by romperstomper; 07-02-2012 at 07:17 AM.
Break on unhandled errors.
If GetValue(Sheet1.Cells(4, x)) = PairVals(0, j) Then 'No change
Have also tried put a numeric value in the place of the link just to be sure. The array takes its' values from row 4 which is a copy of the link value anyway.
So v strange and still no idea
Last edited by futurespec; 07-02-2012 at 07:31 AM.
I'm stumped. The GetValue function should override any errors and just return 0 for that case so I can't see where a type mismatch occurs, and I can't replicate it in your workbook.
You're not using x and j for anything else are you?
No, you have the full code.
Kept it brief to try and isolate the problem.
In some way I am pleased that it has stumped a 'guru' 'cos it has had me going around in ever decreasing circles and I do not like where I am ending up!
Ignoring the code for a moment is there some weird setting in XL that might lead to this scenario?
I have not knowingly changed anything but may well have done in the past.
Is it possible to have more than 1 occurrence of the macro running concurrently? and if so how do I prevent that?
No it is not - VBA is single threaded, and I cannot think of any other setting that would allow this behaviour. Let's try and break down the offending line:
What message do you get if you use this version?Please Login or Register to view this content.
cell value .. correct
array val... blank !
Did you Redim As Double?
Yes
As per one of your posts I think
Have you reopened the workbook? If the Open event hasn't run, then your 'array' is actually just an empty variant. If you don't have some other reason for redimensioning it, I would just use:
or use:Please Login or Register to view this content.
if you do need to redimension it on the fly.Please Login or Register to view this content.
Have reopened now and no difference.
Will not allow me to dimension the array in Public section
Great problems trying to edit code ... when I hit the stop button the error message comes up and if I hit end the macro keeps kicking back in 'cos data is updated several times a second and I am unable to stop it unless ultra quick
Last edited by futurespec; 07-02-2012 at 08:44 AM.
Move the declaration to a normal module rather than ThisWorkbook and then alter the GetValue function to:
Please Login or Register to view this content.
First the good news ... it has stopped throwing up the error message :-)
Now the not so good news... it is not copying any of the data down to lower rows
OK, let's add some debugging:
what if anything can you see in the Immediate Window in the VBE? (Ctrl + G to open the Immediate Window if you don't already have it open)Please Login or Register to view this content.
Nothing under immediate (no I did not have it open)
Under watch..
Watch : : PairVals(0, 0) : <Out of context> : Variant/Empty : ThisWorkbook.Workbook_SheetCalculate
Ok added that code....
Now shows the correct value and it changing
If you had no data in row 5, then:
will give you the last row in the sheet. Try:Please Login or Register to view this content.
instead.Please Login or Register to view this content.
MAGIC!!!
Big smiles, big thankyous; you are a star with such great patience. And yes you are a 'Guru'
Really appreciated.
Now could you please explain why I was getting the error in the first instance so that I do not do it again.
I suspect your cells hadn't properly calculated when the sub was running (hence the error value check) but am not sure (and don't use DDE so can't verify that).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks