By Ref argument type mismatch

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
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
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
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.
 
L

Legacy 98055

Guest
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

Well-known Member
Joined
Jun 30, 2005
Messages
2,015

ADVERTISEMENT

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
 
L

Legacy 98055

Guest
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.

Gene_1.gif


Clicking the help button on the messagebox results in this:

Gene_2.jpg


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. :biggrin:
Admit is Gene. Your spoiled! :LOL:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Tom

So your help files actually work?:)
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
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. :biggrin:
Admit is Gene. Your spoiled! :LOL:

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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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}
 

Forum statistics

Threads
1,140,937
Messages
5,703,260
Members
421,289
Latest member
fbohlandt

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top