Home My VBA module outputs the 'wrong' kind of range. Any ideas?

My VBA module outputs the 'wrong' kind of range. Any ideas?

Matt MC56
1#
Matt MC56 Published in 2018-01-13 01:56:10Z
 Good day to all. This is my first time asking a question here so please excuse any etiquette mistakes. I have searched for an answer to my problem but drawn a blank so far, but perhaps I'm asking the wrong question. My VBA knowledge is self-taught and I'm far from a pro. I've developed a couple of functions that will compare an input range of text and/or numbers with text token and return a range of true/false answers, so that I can then use that array in array formulas and so on. The reason is to develop a way of dealing with a problem I have later, this is just a dev project until I can get it working. My code is: Option Explicit Function EvalStr(Inp As String) As Integer ' Dim result As Boolean ' Application.Volatile result = Evaluate(Inp) If result Then EvalStr = 1 Else EvalStr = 0 End If End Function Function EvalRng(TestStr As String, InpRng As Range, Optional ForceHigh As Boolean) As Double() ' Dim OutRng() As Double Dim RngSize As Long Dim ThisStr As String Dim i As Integer ' RngSize = InpRng.Cells.Count ReDim OutRng(RngSize - 1) As Double ' If RngSize > 0 Then For i = 1 To RngSize ThisStr = "=" & InpRng(i).Value2 & TestStr OutRng(i - 1) = EvalStr(ThisStr) Next i End If EvalRng = OutRng End Function  My spreadsheet is attached as an image so you can see what is going on. Spreadsheet A2 is just >=6 entered as text. C2:C11 is a series of incremental numbers, the source data. D2:D11 are some manually entered zeroes and ones, a baseline for the logic of A2. F2:F11 is =EvalStr(C2&$A$2) G2 is =SUMPRODUCT(EvalRng($A$2,C2:C11),C2:C11) D15:F15 is =SUMPRODUCT($C2:$C11,D2:D11) Ignore column E  D15 is the control sample. F15 also works, so my Functions are half right. G2 does not, and I'm stumped as to why, I've reached the limit of my knowledge. When I use the Formula Entry dialog to work out why it's not working, my array result from EvalRng is separated with commas, whereas the other range is separated with semi-colons. So I'm guessing my EvalRng is the 'wrong' kind of array...? How do I fix that? The dialog box clue Many thanks for any tips or assistance. Matt
YowE3K
2#
 If you make your output a two-dimensional array it will work: Function EvalRng(TestStr As String, InpRng As Range, Optional ForceHigh As Boolean) As Double() ' Dim OutRng() As Double Dim RngSize As Long Dim ThisStr As String Dim i As Integer ' RngSize = InpRng.Cells.Count ReDim OutRng(RngSize - 1, 0) As Double ' If RngSize > 0 Then For i = 1 To RngSize ThisStr = "=" & InpRng(i).Value2 & TestStr OutRng(i - 1, 0) = EvalStr(ThisStr) Next i End If EvalRng = OutRng End Function  Alternatively, you can use the formula suggested in a comment by Slai, but you will need to enter that as an array formula (i.e. press Ctrl+Shift+Enter rather than just Enter when entering the formula): {=SUMPRODUCT(TRANSPOSE(EvalRng($A$2,C2:C11)),C2:C11)}