Optional Variable with Range and String inside VBA code

Question

I would like to ask whether in VBA in Excel there is a possibility to store a part of the code for example inside in string, what I mean is shown in the example below (this code doesn't work):

Sub newMacro()

Dim wb As Workbook
Dim ws As Worksheet
Dim sAdditional As String

Dim rngWhereCount_1 As Range
Dim rngWhereCount_2 As Range

Dim iCellValue As Integer
Dim i as integer

Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)

Set rngWhereCount_1 = ws.Columns(1)
Set rngWhereCount_2 = ws.Columns(2)

For i = 1 To 10
    If (i = 1) Or (i = 2) Then
        sAdditional = ", rngWhereCount_2, i"
    Else
        sAdditional = ""
    End If

    iCellValue = Application.WorksheetFunction.CountIfs(rngWhereCount_1, 1 & sAdditional)
Next i


End Sub

So the question is if there is an easy and clever way to declare sAdditional (maybe not as string), but to make it optional inside the loop, and at the same time sAdditional contains Range and string inside...

Thanks in advance! P.

I know that the CountIf function takes in a Range and a Range takes in a String so there might be something to that there... cause you can store extra ranges as Strings and just call Range(strValue)... — chancea
– chancea, Commented Aug 5, 2013 at 15:49
The problem is that sAdditional is not always Range or String, but the combination of these two. — Purchawka
– Purchawka, Commented Aug 5, 2013 at 15:51

Ioannis · Accepted Answer · 2013-08-05 17:21:25Z

1

I think it is not possible to have a variable containing a String and a Range at the same time.. Assuming that you are counting positive numbers, you can do the following:

Define a string variable strCriterion2. Then strCriterion2 = IIf(i=1 or i=2,"i",">=0"). So strCriterion2 equals either "i" or ">=0", depending on the value of i.
Write your CountIfs as CountIfs(rngWhereCount_1, 1, rngWhereCount_2, strCriterion2).

The idea is that whenever we do not want the second criterion to be activated, we enter a criterion that we know is valid for all the entries in the range. So if all entries are positive numbers, the second criterion will be cancelled out if we impose ">=0". In case you want to include negative numbers, you can use ">=-2.2251E-308" instead of ">=0" (depending also on which version of excel you have, the number I give is lowest for Excel 2003).

I hope the above helps.

answered Aug 5, 2013 at 17:21

Ioannis

5,4682 gold badges21 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Purchawka Over a year ago

yes, I guess this is a solution, however, it was just an example, and I have it with strings, but it will work with strCriterion2="<> "" " assuming all the variables are not empty, so thanks a lot! It helps, cheers!

Ioannis Over a year ago

Glad to know it helped and thanks for the comment, something to learn for me as well!

Purchawka Over a year ago

sorry I've made a mistake, it should be just strCriterion2="<>", thanks for cooperation!

Collectives™ on Stack Overflow

Optional Variable with Range and String inside VBA code

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related