By Ref argument type mismatch

[mortgageman]

I am getting this error in the following code in the call to allroots. It is not clear to me why I should get this error.

Gene, "The Mortgage Man", Klein


Private Sub Go_Click()
Dim a, b, c As Long ' the a,b and c of ax2+bx+c
Dim aos As Double ' Axis Of Symmetry
Dim x1, x2 As Double 'The two roots
Dim determ As Double
Dim line1xarray, line1yarray
Dim y1, y2, y3, y4, y5, y6, y7
Dim currentchart, fname

'Retrieve the Coefficients of the quadratic
a = TextBox1.Value
b = TextBox2.Value
c = TextBox3.Value

'Calculate the Axis of Symmetry
aos = -1 * b / (2 * a)
TextBox4.Value = aos

'Calculate y value of vertex
TextBox5.Value = a * aos ^ 2 + b * aos + c

'Determine if function "holds" or "spills" water
TextBox6.Value = "UP"
If a < 0 Then TextBox6.Value = "DOWN"

'Determine if function is wider or narrower than y=x^2
TextBox7.Value = "WIDER"
If Abs(a) > 1 Then TextBox7.Value = "NARROWER"

'Express in Vertex form
TextBox8.Value = a & "(x-" & aos & ")^2+" & TextBox5.Value

'Make sure that there are no complex roots
determ = b ^ 2 - 4 * a * c
If determ < 0 Then

TextBox12.Value = "The rest is too complex for me"
Exit Sub
End If

'Calculate first root
MsgBox allroots(a, b, c, 0)
x1 = (-b + (b ^ 2 - 4 * a * c) ^ 0.5) / (2 * a)
TextBox10.Value = x1
MsgBox allroots(a, b, c, 1)
'Calculate second root
x2 = (-b - (b ^ 2 - 4 * a * c) ^ 0.5) / (2 * a)
TextBox11.Value = x2

'Express in Intercept form
TextBox9.Value = a & "(x-" & x1 & ")(x-" & x2 & ")"


'Generate the Graph

'First clean up any graphs that may already exist:
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
Do Until ActiveChart.SeriesCollection.Count = 0
ActiveChart.SeriesCollection(1).Delete
Loop


'create the arrays for the inputs to the charts
line1xarray = Array(aos - 3, aos - 2, aos - 1, aos, aos + 1, aos + 2, aos + 3)
y1 = a * ((aos - 3) ^ 2) + b * (aos - 3) + c
y2 = a * ((aos - 2) ^ 2) + b * (aos - 2) + c
y3 = (a * ((aos - 1) ^ 2)) + b * (aos - 1) + c
y4 = a * (aos ^ 2) + b * aos + c
y5 = a * ((aos + 1) ^ 2) + b * (aos + 1) + c
y6 = a * ((aos + 2) ^ 2) + b * (aos + 2) + c
y7 = a * ((aos + 3) ^ 2) + b * (aos + 3) + c
line1yarray = Array(y1, y2, y3, y4, y5, y6, y7)

'put on line 1
With ActiveChart.SeriesCollection.NewSeries
.Name = "Line 1"
.XValues = line1xarray
.Values = line1yarray
End With

'put on line 2
With ActiveChart.SeriesCollection.NewSeries
.Name = "Line 2"
.XValues = Array(aos, aos, aos, aos, aos, aos, aos)
.Values = line1yarray
End With

'Title the Chart
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "y=ax^2+bx+c"
End With

'put Horizontal and Vertical Gridline
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With

'Write the Chart to the spreadsheet
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

'Write graph to a file and then read it into the UF image
'Set currentchart = Sheets("Sheet1").ChartObjects(Sheets("Sheet1").ChartObjects.Count).Chart
'fname = ThisWorkbook.Path & "\temp.gif"
'currentchart.Export Filename:=fname, Filtername:="GIF"
'Image1.Visible = True
'Image1.Picture = LoadPicture(fname)

End Sub

Private Function q(a As Long, b As Long, c As Long, ByVal xval) As Long
q = a * xval ^ 2 + b * xval + c
End Function
Public Function allroots(a As Long, b As Long, c As Long, posneg As Boolean)
Dim determ As Double
Dim real, img As Double
determ = b ^ 2 - 4 * a * c
If determ < 0 Then
real = -b / (2 * a)
determ = -determ
img = (determ ^ 0.5) / (2 * a)
If (posneg = 0) Then img = -img
allroots = complex(real, img)
Exit Sub
End If
If posneg = 0 Then
allroots = (-b - (b ^ 2 - 4 * a * c) ^ 0.5) / (2 * a)
Else: allroots = (-b + (b ^ 2 - 4 * a * c) ^ 0.5) / (2 * a)
End If
End Function

 

[Norie]

Gene

I don't know if it's the problem but the following only dim's c as Long, a and b will be Variants.

Dim a, b, c As Long ' the a,b and c of ax2+bx+c

Try this.

Dim a As Long, b As Long, c As Long ' the a,b and c of ax2+bx+c

 

[mortgageman]

Norie - Someone is on the ball today - and it sure as heck aint me!

Gene, "The Mortgage Man", Klein

Edit: You know - Excel should have a better error message for that. I mean the argument was of the wrong type, but it still was being passed by refrerence.

 

[Legacy 98055]

Gene. The error provides a good description of what is wrong. Arguments are passed ByRef by default. It is a compile time error. You cannot provide a narrowing conversion as an argument. A long cannot contain a variant ByRef or ByVal. The following would have compiled because it is a widening conversion.

Private Sub Go_Click()
Dim a As Long, b As Long, c As Long ' the a,b and c of ax2+bx+c

Public Function allroots(a As Variant, b As Variant, c As Variant, posneg As Boolean)

A variant can contain a copy-of or reference a long.

 

[mortgageman]

I may be way out of my depth here (Heck I AM way out of my depth here), but why didn't excel give me an error message more along the lines of your (very good) explanation? I searched and searched for where I was sending a value instead of a refrence to the function. And what does compile time have to with anything? (I said I was out of my depth!)

Gene, "The Mortgage Man", Klein

 

[Legacy 98055]

I am getting this error in the following code in the call to allroots. It is not clear to me why I should get this error.

Please take this in the light-hearted manner that it is being delivered.

At the risk of sounding obsessive, I suppose my post was subconciously pressing because I am a bit defensive when the helps are ridiculed. It is the norm for the "gurus" and the Excel community at large to berate what I think is an excellent help system that somebody put a lot of consideration into. I appreciate that. I learned how to program using these helps long before I knew what a messageboard was. Anyway, the compile error and the reponse of the help system is typical. Your answer was readily available because the information you needed was clearly stated. Note that even the problem is highlighted, a box pops up with an intentionally brief description, and a welath of knowledge is waiting to be discovered with the click of the "HELP!" button.

Clicking the help button on the messagebox results in this:

Even going on the assumption that this developer does not have a clue as to what "ByRef", "Argument", "Type", or "Mistmatch" defines, the answers are all there. Every one of them clearly explained with a click on one or more hyperlinks. And the "See Also" hyperlink providing even more background.

My mention of "Compile Error" was just to note that this was an obvious error. Not a runtime or logic error which can be a bit elusive.

Then again, who needs the help files when you have Norie.
Admit is Gene. Your spoiled!

 

[Norie]

Tom

So your help files actually work?

 

[erik.van.geit]

Tom,

This was a wonderful reply going to my Favorites!

best regards,
Erik

 

[mortgageman]

I am getting this error in the following code in the call to allroots. It is not clear to me why I should get this error.

Please take this in the light-hearted manner that it is being delivered.


Then again, who needs the help files when you have Norie.
Admit is Gene. Your spoiled!

No arm twisting needed for me to admit that Norie (and everyone on this great board) had spoiled me rotten. In my own meager defense however, I should note I did try clicking on the help button. However, as Norie alluded to, all I got was a blank screen - not the very helpful material that you clearly get. Oh well - where would we be if microsoft didn't give us reason to bash them

Gene, "The Mortgage Man", Klein

 

[tusharm]

Bravo! With a few exceptions I couldn't agree with you more.

Berating MS help is just the popular thing to do -- well, with one exception: the search capability of Office 2003, which deserves all the abuse that is heaped on it and then some more.

For an example of how one can leverage help to one's advantage, even with the crappy search capability, see
Case Study – Understanding code
http://www.tushar-mehta.com/excel/vba/vba-Understand code.htm

And, if it the information isn't available in the help file, it's worth searching msdn.microsoft.com or support.microsoft.com.

{snip}
At the risk of sounding obsessive, I suppose my post was subconciously pressing because I am a bit defensive when the helps are ridiculed. It is the norm for the "gurus" and the Excel community at large to berate what I think is an excellent help system that somebody put a lot of consideration into. {snip}