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

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#
YowE3K Reply to 2018-01-13 02:32:09Z

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)}
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.340675 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO