# By Ref argument type mismatch

#### mortgageman

##### Well-known Member
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:
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

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

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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.
Code:
``Dim a, b, c As Long ' the a,b and c of ax2+bx+c``
Try this.
Code:
``Dim a As Long, b As Long, c As Long ' the a,b and c of ax2+bx+c``

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.

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.

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

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.

Tom

So your help files actually work?

Tom,

This was a wonderful reply going to my Favorites!

best regards,
Erik

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.

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

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}

Replies
7
Views
218
Replies
0
Views
498
Replies
6
Views
136
Replies
5
Views
441
Replies
1
Views
588

1,219,811
Messages
6,150,362
Members
450,955
Latest member
rose8693

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back