Expected list separator or ошибка

Weighted Average UDF

• Whatever you’re doing here, according to this link, that’s not how you calculate the weighted average (the last parameter seems redundant, dividing in the loop is wrong).
• The suggested SUMPRODUCT/SUM formula solution in the provided link will fail if there are cells not containing numeric values while the following function will ignore (skip) those cells.

Weighted Average

Option Explicit

Function WeightedAverage( _
    ByVal ScoreColumnRange As Range, _
    ByVal WeightColumnRange As Range) _
As Double
    
    ' Compare the number of rows and use the smaller number.
    Dim rCount As Long: rCount = ScoreColumnRange.Rows.Count
    Dim wrCount As Long: wrCount = WeightColumnRange.Rows.Count
    If wrCount < rCount Then rCount = wrCount
    
    ' Create the references to the column ranges.
    Dim srg As Range: Set srg = ScoreColumnRange.Cells(1).Resize(rCount)
    Dim wrg As Range: Set wrg = WeightColumnRange.Cells(1).Resize(rCount)
    
    ' Write the values from the column ranges to arrays.
    Dim sData As Variant, wData As Variant
    If rCount = 1 Then
        ReDim sData(1 To 1, 1 To 1): sData(1, 1) = srg.Value
        ReDim wData(1 To 1, 1 To 1): wData(1, 1) = wrg.Value
    Else
        sData = srg.Value
        wData = wrg.Value
    End If
    
    ' Declare additional variables to be used in the For...Next loop.
    Dim sVal As Variant, wVal As Variant ' Current Values
    Dim r As Long ' Rows Counter
    Dim tWeight As Double ' Total Weight
    Dim tProduct As Double ' Total Sum
    
    ' Calculate the total weights and the total products.
    For r = 1 To UBound(sData, 1)
        sVal = sData(r, 1)
        If IsNumeric(sVal) Then ' prevent invalid score
            wVal = wData(r, 1)
            If IsNumeric(wVal) Then ' prevent invalid weight
                tWeight = tWeight + wVal
                tProduct = tProduct + sVal * wVal
            End If
        End If
    Next r
    If tWeight = 0 Then Exit Function ' all were invalid
    
    ' Calculate and return the weighted average (the result).
    WeightedAverage = tProduct / tWeight ' maybe you want to round?

End Function

Your Code Revised

Function WeightedAverage( _
    ByVal DataColumnRange As Range, _
    ByVal ValuesColumnRange As Range, _
    ByVal TotalValue As Double) _
As Double
    
    ' Compare the number of rows and use the smaller number.
    Dim rCount As Long: rCount = DataColumnRange.Rows.Count
    Dim vrCount As Long: vrCount = ValuesColumnRange.Rows.Count
    If vrCount < rCount Then rCount = vrCount
    
    ' Create the references to the column ranges.
    Dim drg As Range: Set drg = DataColumnRange.Cells(1).Resize(rCount)
    Dim vrg As Range: Set vrg = ValuesColumnRange.Cells(1).Resize(rCount)
    
    ' Write the values of the column ranges to arrays.
    Dim dData As Variant, vData As Variant
    If rCount = 1 Then
        ReDim dData(1 To 1, 1 To 1): dData(1, 1) = drg.Value
        ReDim vData(1 To 1, 1 To 1): vData(1, 1) = vrg.Value
    Else
        dData = drg.Value
        vData = vrg.Value
    End If
    
    ' Declare additional variables to be used in the For...Next loop.
    Dim dVal As Variant, vVal As Variant ' Current Values
    Dim r As Long ' Rows Counter
    Dim tCount As Long ' Total Count
    Dim Total As Double ' Total Value
    
    ' Calculate the Total? (there should be a math term for it)
    For r = 1 To UBound(dData, 1)
        dVal = dData(r, 1)
        If IsNumeric(dVal) Then ' prevent invalid data
            vVal = vData(r, 1)
            If IsNumeric(vVal) Then ' prevent invalid value
                tCount = tCount + 1
                Total = Total + dVal * vVal / TotalValue
            End If
        End If
    Next r
    
    If tCount = 0 Then Exit Function ' all were invalid
    
    ' Calculate and return the weighted average (the result).
    WeightedAverage = Total / tCount ' maybe you want to round?

End Function

The Expected: list separator or ) error message tells you that the compiler was expecting to find either a list separator (such as the comma that separates arguments in a function) or a closing parenthesis in the statement. In most cases, it highlights where the problem began. For example, the following statement, when compiled, generates an Expected: list separator or ) error message with the word World highlighted:

Answer = MsgBox(Hello World, vbInformation,»Test»)

The problem with the preceding line is that the words Hello World are supposed to be a string literal enclosed in quotation marks, but we forgot the quotation marks. The blank space between the words Hello and World has sent the compiler into a tizzy because it was expecting something else there. To correct the problem, put the quotation marks around the string literal:

Answer = MsgBox(«Hello World», vbInformation,»Test»)

With the quotation marks in place, the compiler can see that the entire string of text «Hello World» is the first argument, vblnformation is the second argument, and «Test» is the third argument.

Sometimes the Expected: list separator or ) error message points out a missing parenthesis in a statement. For example, the following statement generates such an error message when compiled:

PCase = «Mc» & UCase(Mid(PCase, 3, 1) & Mid(PCase, 4)

It’s rarely easy to see where a parenthesis needs to be added to a statement, especially if the statement contains lots of them. One fact is always true, though: Any statement that uses open parentheses must also use an equal number of closed parentheses.

Here’s a little trick that programmers use to see whether they have the right number of parentheses. You start with the number 0 in mind. Then you read from left to right. Each time you encounter an open parenthesis, add 1 to that 0. Each time you come to a closed parenthesis, subtract 1 from that number. By the time you get to the end of the line, you should be back to 0. If you end up at any other number, you have a problem.

As an example, Figure 12-7 shows the preceding troublesome line after counting open and closed parentheses. After you add 1 for each open parenthesis and subtract 1 for each closing parenthesis, you end up with 1. That number shows that you either have one too many open parentheses or you’re lacking one closed parenthesis.

Needless to say, you can’t just stick an extra closing parenthesis into the statement at random. Rather, you need to understand the syntax rules of the various functions used in the expression. The example in Figure 12-7 uses two functions named UCase() and Mid(). Each function needs its own, complete pair of parentheses.

Figure 12-7:

Counting open and closed parentheses in a statement.

0 +1 +1 -1 +1 -1 =1 II III Uh~°h

PCase — «Mc» & Ucase(Mid(PCase, 3, 1) & Mid(PCase, 4)_

The Mid(PCase, 4) function at the end of the statement is fine because the Mid() function requires exactly one open and one closed parenthesis. The larger Mid() function, Mid(PCase, 3, 1), is also okay because it has one open and one closed parenthesis.

The problem occurs with the UCase() function. That larger Mid(PCase, 3, 1) function is the argument for the UCase() function, and there’s no closing parenthesis for UCase(). That needs to be added right after the closing parenthesis for Mid(). Each of the Mid() functions also has a pair of open and closed parentheses. If you count the parentheses in the modified statement shown in Figure 12-8, the count ends up at 0, which is exactly what you want.

Figure 12-8:

Equal number of open and closed parentheses.

Regardless of which compile error message you get, you have to fix the problem before you can even run the procedure. Don’t expect the compile error message to pinpoint the solution for you. The message in a compile error is often too vague and too general for that. In most cases, your only recourse is to look up the correct syntax in Help (or through the Object Browser) and apply it to whatever you’re trying to accomplish.

Continue reading here: Checking on Variables with Debug Print

Was this article helpful?

Call Shell("cmd.exe /S /k " & ""C:\Program Files\Java\jre1.8.0_171\bin\javaw.exe" -jar " & DPath & "  """ & inp1 & """ """ & inp2 & """ """ & sPath & """ """ & FilePath & """", vbNormalFocus)

3 ответа

Лучший ответ

Call Shell("cmd.exe /S /K " & """C:\Program Files\Java\jre1.8.0_171\bin\javaw.exe"" -jar " & DPath & "  """ & inp1 & """ """ & inp2 & """ """ & sPath & """ """ & FilePath & """", vbNormalFocus)

Call Shell("cmd.exe /S /K " & """""C:\Program Files\Java\jre1.8.0_171\bin\javaw.exe"" -jar " & DPath & "  """ & inp1 & """ """ & inp2 & """ """ & sPath & """ """ & FilePath & """""", vbNormalFocus)

Call Shell("cmd.exe /S /K " & """""C:\Program Files\Java\jre1.8.0_171\bin\javaw.exe"" -jar """ & DPath & """ """ & inp1 & """ """ & inp2 & """ """ & sPath & """ """ & FilePath & """""", vbNormalFocus)

Call Shell("cmd.exe /S /k ""C:\Program Files\Java\jre1.8.0_171\bin\javaw.exe"" -jar " & _
           DPath & "  """ & inp1 & """ """ & inp2 & """ """ & sPath & """ """ & _
            FilePath & """ ", vbNormalFocus)

Dim qDPath As String
Dim qinp1 As String
Dim qinp2 As String
Dim qsPath As String
Dim qFilePath As String
Dim qCommand As String

qDPath = MakeQuotedString(Path)
qinp1 = MakeQuotedString(inp1)
qinp2 = MakeQuotedString(inp2)
qsPath = MakeQuotedString(sPath)
qFilePath = MakeQuotedString(FilePath)

qCommand = MakeQuotedString("C:\Program Files\Java\jre1.8.0_171\bin\javaw.exe")
qCommand = MakeQuotedString("cmd.exe /S /k " & qCommand & "-Jar " & qDPath & qinp1 & qinp2 & qsPath & qFilePath)

Call Shell(qCommand, vbNormalFocus)

Call Shell("cmd.exe /S /k " & ""C:\Program Files\Java\jre1.8.0_171\bin\javaw.exe" -jar " & DPath & "  """ & inp1 & """ """ & inp2 & """ """ & sPath & """ """ & FilePath & """", vbNormalFocus)

Dim ShellCmd as String
ShellCmd = Fmt("cmd.exe /S /k {dq}{dq}C:\Program Files\Java\jre1.8.0_171\bin\javaw.exe{dq} -jar {dq}{0}{dq}  {dq}{1}{dq} {dq}{2}{dq} {dq}{3}{dq} {dq}{4}{dq}{dq}", dpath, inp1, inp2, sPath, FilePath)

Call Shell(ShellCmd, vbNormalFocus)