Type mismatch

User avatar
Peter Kinross
5StarLounger
Posts: 962
Joined: 09 Feb 2010, 00:33
Location: Patterson Lakes, Victoria, Australia

Type mismatch

Post by Peter Kinross »

I have a user defined function nRtn in my Excel sheet.
=nRtn(Mnth3,Mnth6,Year1,Year3,Year5,MStar).
Mnth3 and Mnth6 are not passed on to the Function. When I do a ? Mnth3 (or Mnth6) in the immediate widow I get a ‘Type mismatch’. If I let it run, I get the same error as soon as the function tries to use either Mnth3 or Mnth6. The other inputs, Year1 etc, are passed perfectly.
In the sheet cells all of the function inputs are set as ‘Number’ 2 dec places.
I tried the Function with 'as Single' data type and with no set data type. Got the errors both ways.
The names are correct. If I substitute the cell ref for the names I get the same error.
I am at a loss, HELP.
Avagr8day, regards, Peter

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Type mismatch

Post by HansV »

Could you attach a sample workbook with the function and some dummy data?
Best wishes,
Hans

User avatar
Peter Kinross
5StarLounger
Posts: 962
Joined: 09 Feb 2010, 00:33
Location: Patterson Lakes, Victoria, Australia

Re: Type mismatch

Post by Peter Kinross »

Here 'tis
You do not have the required permissions to view the files attached to this post.
Avagr8day, regards, Peter

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Type mismatch

Post by HansV »

Mnth3 etc. are multi-cell ranges.

Your function Score passes Mnth3 etc. to AvReturns. In this function, you try to perform calculations such as

TotRtns = TotRtns + rMnth3

but that makes no sense for a multi-cell range. Change the formula in J2 to

=Score(D2,E2,F2,G2,H2,L2)

and fill down. Alternatively, convert the range to a table. You can then use

=Score([@3MTH],[@6MTH],[@1YR],[@3YR],[@5YR],[@[M
Star]])


(Note the line feed in the formula)
Best wishes,
Hans

User avatar
Peter Kinross
5StarLounger
Posts: 962
Joined: 09 Feb 2010, 00:33
Location: Patterson Lakes, Victoria, Australia

Re: Type mismatch

Post by Peter Kinross »

Thanks Hans.
I thought that Excel would be smarter than that, after all cells can work out what the name is. One would think that that value could be passed on.
Your solution is what I originally used, but changed to names to make the theory easier for me to grasp again in 6 months time.
Converting the data to a table would not fit the next lot of data. Then I would have to recreate the table, too much trouble, I'll stick with cell references.
I don't suppose that there is any way of using ByVal? I tried a couple of ways, but no dice.
BTW, you say to "Note the line feed in the formula". I can't see a line feed.
Thanks again Hans
Avagr8day, regards, Peter

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Type mismatch

Post by HansV »

In a formula that uses only built-in functions, you can refer to a multi-cell reference and if possible, Excel will use the value of the cell in the same row as the formula cell.
But your function uses

TotRtns = TotRtns + rMnth3

which is equivalent to

TotRtns = TotRtns + rMnth3.Value

But that will cause a Type Mismatch error if rMnth3 contains multiple cells, since the Value of a multi-cell range is an array, not a number.

Cell L1 contains "M<line feed>Star", so you see

M
Star

The formula with structured table references has a line feed between M and Star too.
Best wishes,
Hans

User avatar
Peter Kinross
5StarLounger
Posts: 962
Joined: 09 Feb 2010, 00:33
Location: Patterson Lakes, Victoria, Australia

Re: Type mismatch

Post by Peter Kinross »

Dang, for all the other named ranges I just named the data, with that one I included the heading as well.
Fair dinkum Hans, you don't miss much - incredible.
Still amused that Excel doesn't have the nouse to recognize that I am passing the value not the whole array.
I gather that ByVal won't help.
ThanksHansStamp.gif
You do not have the required permissions to view the files attached to this post.
Avagr8day, regards, Peter

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Type mismatch

Post by Doc.AElstein »

Hi Peter
This reply might give you some other ideas to think about.
It is just some ideas, that’s all. I don’t have a ( useful ) working solution for you.
_.___
It seems that the problem is that you are taking into your function ranges, but what you wanted was the values from those ranges at approximately the row where the function was.
In some circumstances, as Hans explained, Excel is indeed smart enough to do something similar what you want, but not how you are doing things.

The idea I can offer you to think about is to change your functions a bit so as to pick out the correct value from the ranges. It won’t directly solve your problem as I have done it, but maybe it might give some ideas, that’s all.

Looking at the names manager , it seems that you have
Mnth1 $C$2:$C$312
Mnth3 $D$2:$D$312
Mnth6 $E$2:$E$312
Year1 $F$2:$F$312
Year3 $G$2:$G$312
Year5 $H$2:$H$312

MStr $L$1:$L$312
( In addition to that, you take in some of those ranges into another function , nX, in a variable Rtn. So Rtn is going to be subject to the same basic issue that you are having).

As Hans pointed out, at various points you try to use those ranges, hoping Excel will guess what value from that range that you want. The problem is that Excel has no way of knowing what value to take, and in addition you have that complication that Hans mentioned with MStr

As Hans Pointed out, if you use a multi-cell range as you are doing, for example with rMnth3, then it returns you an array of values. Usually/ often in coding , using something like your rMnth3 will actually default to like this
rMnth3.Value
That is an array, pseudo like
MyArray()=rMnth3.Value
What you want at any point is a specific value in that array. Like MyArray(1, 1 ) or myArray(2, 1) … etc.
You can get at these with these sorts of syntax
rMnth3.Value()(1, 1)
rMnth3.Value()(ActiveCell.Row - 1, 1)


( That extra () is not a typo. It won’t work without that. Sometimes in these situations you do or don’t need that. I can’t quite figure out why, it seems like no one can )

The modified macros below and in the returned file will sort of do what you want, but not in a way of much practical use:
If you write ( re write ) the function formula Score in any cell, just as you have them, then it will work and give you the correct answer.
So this solution as it stands is not much use practically. But maybe it could help in some part of lateral thinking… Or not.. :)
( Someone smarter than me might be able to think of a way to use this basic idea to make it work a bit more usefully )

Alan

Code: Select all

Option Explicit
'Set importance factors for various return periods and Morningstar
Const iMnth3 As Single = 0, iMnth6 As Single = 1, fMstr As Single = 3
Const iYear1 As Single = 0.9, iYear3 As Single = 0.8, iYear5 As Single = 0.7
Const AllowedAvDif As Single = 3 '= portion of Av that Rtn can differ bbefore we normalize
Const DiffAdjust As Single = 5 '= portion of Av that we change Rtn to normalize it

Public Function Score(rMnth3, rMnth6, rYear1, rYear3, rYear5, MStr)
'Normanlizes returns that are more than 33% away from average.
'Reduces effect of one year wonder funds
'M6 = 6 month return, Y1 = 1 year return etc
Dim AvRtn As Single, nAvRtn As Single
Dim nMnth3 As Single, nMnth6 As Single, nXear1 As Single, nXear3 As Single, nXear5 As Single
'On Error GoTo Err_Score
'Get average
AvRtn = AvReturns(rMnth3, rMnth6, rYear1, rYear3, rYear5)
'If Y is > AvRtn/10 above AvRtn then reduce it
nMnth3 = nX(rMnth3, AvRtn)
nMnth6 = nX(rMnth6, AvRtn)
nXear1 = nX(rYear1, AvRtn)
nXear3 = nX(rYear3, AvRtn)
nXear5 = nX(rYear5, AvRtn)
nAvRtn = nMnth3 + iMnth3 + nMnth6 * iMnth6 + nXear1 * iYear1 + nXear3 * iYear3 + nXear5 * iYear5
If nAvRtn < 0 Then  'Leave -ve (ie ignore MStar)
    Score = nAvRtn
Else
    Score = nAvRtn + MStr.Value()(ActiveCell.Row, 1) * fMstr
End If
Exit_Score:
Exit Function

'Err_Score:
''MsgBox Err.Description
'GoTo Exit_Score
'Resume Next
End Function

Function AvReturns(rMnth3, rMnth6, rYear1, rYear3, rYear5)
'Gets average of all returns that are being considered (IE with iMnth3 etc >0)
Dim Cntr As Integer
Dim TotRtns As Single
If iMnth3 > 0 Then
    Cntr = Cntr + 1
    TotRtns = TotRtns + rMnth3.Value()(ActiveCell.Row - 1, 1)
End If
If iMnth6 > 0 Then
    Cntr = Cntr + 1
    TotRtns = TotRtns + rMnth6.Value()(ActiveCell.Row - 1, 1)
End If
If iYear1 > 0 Then
    Cntr = Cntr + 1
    TotRtns = TotRtns + rYear1.Value()(ActiveCell.Row - 1, 1)
End If
If iYear3 > 0 Then
    Cntr = Cntr + 1
    TotRtns = TotRtns + rYear3.Value()(ActiveCell.Row - 1, 1)
End If
If iYear5 > 0 Then
    Cntr = Cntr + 1
    TotRtns = TotRtns + rYear5.Value()(ActiveCell.Row - 1, 1)
End If
AvReturns = TotRtns / Cntr
End Function

Function nX(Rtn, AvRtn)
'Normalizes return if too far away from average
'If both +ve, normalize Rtn
If Rtn.Value()(ActiveCell.Row - 1, 1) < 0 And AvRtn < 0 Then
    If Abs(Rtn.Value()(ActiveCell.Row - 1, 1)) - Abs(AvRtn) > Abs(AvRtn) / AllowedAvDif Then
        nX = Rtn.Value()(ActiveCell.Row - 1, 1) - AvRtn / DiffAdjust
    ElseIf Abs(AvRtn) - Abs(Rtn) > Abs(AvRtn) / AllowedAvDif Then
        nX = Rtn.Value()(ActiveCell.Row - 1, 1) + AvRtn / DiffAdjust
    Else
        nX = Rtn.Value()(ActiveCell.Row - 1, 1)
    End If
'If both +ve, normalize Rtn
ElseIf Rtn.Value()(ActiveCell.Row - 1, 1) >= 0 And AvRtn >= 0 Then
    If Rtn.Value()(ActiveCell.Row - 1, 1) - AvRtn > AvRtn / AllowedAvDif Then
    nX = Rtn.Value()(ActiveCell.Row - 1, 1) - AvRtn / DiffAdjust
    ElseIf AvRtn - Rtn.Value()(ActiveCell.Row - 1, 1) > AvRtn / AllowedAvDif Then
        nX = Rtn.Value()(ActiveCell.Row - 1, 1) + AvRtn / DiffAdjust
    Else
        nX = Rtn.Value()(ActiveCell.Row - 1, 1)
    End If
'If Rtn and Av have 1 +ve and the other -ve, leave as is.
'-ve Av will get a score of 0 (in calling Function above)
'for -ve Rtn, don't normalize, let it influence score accordingly.
End If
End Function
Alan
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Peter Kinross
5StarLounger
Posts: 962
Joined: 09 Feb 2010, 00:33
Location: Patterson Lakes, Victoria, Australia

Re: Type mismatch

Post by Peter Kinross »

Thanks Doc. Very interesting insights.
But 'ActiveCell.Row' returns whichever row was last altered (as it is supposed to) and uses that for all rows giving the same answer (Score) for every row.
Looks like I'm stuck with using cell references not names.
I am still bewildered that I can use those names in lengthy formulas or Excel functions and the correct value is used, but put that identical name into a user defined function and Excel gets lost.
I take it that 'ByVal' is not applicable in any way here.
Avagr8day, regards, Peter

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Type mismatch

Post by HansV »

You are correct that ByVal won't help.
Best wishes,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Type mismatch

Post by Doc.AElstein »

Hi Peter
Your correct about the ActiveCell and the coding I gave will work on that giving the result for the data on the Row of the ActiveCell. It will give the correct results for each row when the formula is used/ typed into any row, as that Cell you type into then becomes the ActiveCell, and correspondingly the Function works on the correct row data, but as I said that’s not directly of any practical use, I was just throwing some ideas around.
For example, another use of the same basic idea:
You include in your function the use of an extra variable, Rwo. This variable will be of the Long type, and is intended to have the row number of where the function is.
Public Function Score(Rwo, rMnth3 , rMnth3 …………………………
( You could have a variable outside your functions to take this value given to the function so that it could also be used in other functions as well )
In your test data sample, you could use a function such as Row(A2) when calling your function, like
=Score(ROW(A2),Mnth3,Mnth6,Year1,Year3,Year5,MStar)

You would put that formula in cell J2 and drag it down.
The modified macro is below. Also it is in the uploaded file along with the fully working formulas in column J
_._______________________________
Peter Kinross wrote:
28 Sep 2020, 01:12
..I take it that 'ByVal' is not applicable in any way here.
I assume you are talking about ByVal as opposed to ByRef, ( ByRef is the default, so is what you have been using) in the signature line of a function.
As Hans said that’s no help. It’s a different thing entirely. The difference between ByVal and ByRef is something fundamental to the understanding of how Functions work. It is very easy when you understand it but quite difficult to grasp the first time. I would strongly recommend learning what that is about if you are writing functions. Without knowing about that it’s a bit like using VBA and not understanding what variable types are.
I won’t bore you with an explanation here, unless you ask. There is plenty of stuff on the internet, Tutorials and the such, just google something like “ByVal or ByRef VBA” and I expect you will be flooded with info

Alan

_.__________________
P.S. I personally use ByVal unless I have a specific reason for using ByRef , and declare all my variable in the signature line as I have done below. That’s just a personal preference, that’s all. ( It is not part of anything I have been trying to explain to you . It has no direct effect on any of the ideas I have been trying to get across )

Code: Select all

Option Explicit
'Set importance factors for various return periods and Morningstar
Const iMnth3 As Single = 0, iMnth6 As Single = 1, fMstr As Single = 3
Const iYear1 As Single = 0.9, iYear3 As Single = 0.8, iYear5 As Single = 0.7
Const AllowedAvDif As Single = 3 '= portion of Av that Rtn can differ bbefore we normalize
Const DiffAdjust As Single = 5 '= portion of Av that we change Rtn to normalize it
Dim Rw As Long
Public Function Score(ByVal Rwo As Long, ByVal rMnth3 As Range, ByVal rMnth6 As Range, ByVal rYear1 As Range, ByVal rYear3 As Range, ByVal rYear5 As Range, ByVal MStr As Range)
 Let Rw = Rwo  '   Have a variable outside your functions to take this value given to the function so that it could also be used in other functions
'Normanlizes returns that are more than 33% away from average.
'Reduces effect of one year wonder funds
'M6 = 6 month return, Y1 = 1 year return etc
Dim AvRtn As Single, nAvRtn As Single
Dim nMnth3 As Single, nMnth6 As Single, nXear1 As Single, nXear3 As Single, nXear5 As Single
'On Error GoTo Err_Score
'Get average
AvRtn = AvReturns(rMnth3, rMnth6, rYear1, rYear3, rYear5)
'If Y is > AvRtn/10 above AvRtn then reduce it
nMnth3 = nX(rMnth3, AvRtn)
nMnth6 = nX(rMnth6, AvRtn)
nXear1 = nX(rYear1, AvRtn)
nXear3 = nX(rYear3, AvRtn)
nXear5 = nX(rYear5, AvRtn)
nAvRtn = nMnth3 + iMnth3 + nMnth6 * iMnth6 + nXear1 * iYear1 + nXear3 * iYear3 + nXear5 * iYear5
If nAvRtn < 0 Then  'Leave -ve (ie ignore MStar)
    Score = nAvRtn
Else
    Score = nAvRtn + MStr.Value()(Rw, 1) * fMstr
End If
Exit_Score:
Exit Function

'Err_Score:
''MsgBox Err.Description
'GoTo Exit_Score
'Resume Next
End Function

Function AvReturns(ByVal rMnth3 As Range, ByVal rMnth6 As Range, ByVal rYear1 As Range, ByVal rYear3 As Range, ByVal rYear5 As Range)
'Gets average of all returns that are being considered (IE with iMnth3 etc >0)
Dim Cntr As Integer
Dim TotRtns As Single
If iMnth3 > 0 Then
    Cntr = Cntr + 1
    TotRtns = TotRtns + rMnth3.Value()(Rw - 1, 1)
End If
If iMnth6 > 0 Then
    Cntr = Cntr + 1
    TotRtns = TotRtns + rMnth6.Value()(Rw - 1, 1)
End If
If iYear1 > 0 Then
    Cntr = Cntr + 1
    TotRtns = TotRtns + rYear1.Value()(Rw - 1, 1)
End If
If iYear3 > 0 Then
    Cntr = Cntr + 1
    TotRtns = TotRtns + rYear3.Value()(Rw - 1, 1)
End If
If iYear5 > 0 Then
    Cntr = Cntr + 1
    TotRtns = TotRtns + rYear5.Value()(Rw - 1, 1)
End If
AvReturns = TotRtns / Cntr
End Function

Function nX(Rtn, AvRtn)
'Normalizes return if too far away from average
'If both +ve, normalize Rtn
If Rtn.Value()(Rw - 1, 1) < 0 And AvRtn < 0 Then
    If Abs(Rtn.Value()(Rw - 1, 1)) - Abs(AvRtn) > Abs(AvRtn) / AllowedAvDif Then
        nX = Rtn.Value()(Rw - 1, 1) - AvRtn / DiffAdjust
    ElseIf Abs(AvRtn) - Abs(Rtn) > Abs(AvRtn) / AllowedAvDif Then
        nX = Rtn.Value()(Rw - 1, 1) + AvRtn / DiffAdjust
    Else
        nX = Rtn.Value()(Rw - 1, 1)
    End If
'If both +ve, normalize Rtn
ElseIf Rtn.Value()(Rw - 1, 1) >= 0 And AvRtn >= 0 Then
    If Rtn.Value()(Rw - 1, 1) - AvRtn > AvRtn / AllowedAvDif Then
    nX = Rtn.Value()(Rw - 1, 1) - AvRtn / DiffAdjust
    ElseIf AvRtn - Rtn.Value()(Rw - 1, 1) > AvRtn / AllowedAvDif Then
        nX = Rtn.Value()(Rw - 1, 1) + AvRtn / DiffAdjust
    Else
        nX = Rtn.Value()(Rw - 1, 1)
    End If
'If Rtn and Av have 1 +ve and the other -ve, leave as is.
'-ve Av will get a score of 0 (in calling Function above)
'for -ve Rtn, don't normalize, let it influence score accordingly.
End If
End Function
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

User avatar
Peter Kinross
5StarLounger
Posts: 962
Joined: 09 Feb 2010, 00:33
Location: Patterson Lakes, Victoria, Australia

Re: Type mismatch

Post by Peter Kinross »

Wow! Nothing short of brilliant Doc.
Sincere thanks.
Great for my current use and also when I look at this again for my next lot of reviews in 6 months time, I will be able to follow the reasoning.
Thanks again.
I was aware of the use of ByVal & ByRef, but was hoping against hope that it could somehow be twisted. It's called clutching at straws. But thanks to your insight there is no need to try twisting anything.
Avagr8day, regards, Peter