I'm trying to get this Evaluate macro to function properly. I'm not very familiar with programming macros, only for very basic tasks. The macro comes from the book "Excel by Example: A Microsoft® Excel Cookbook for Electronics Engineers" by Aubrey Kagan. The macro is suppose to evaluate, based on the rest of the workbook, a resistor's value based on a 4 band resistor. I am having errors when trying to run the macro and I would also like to know how to get the macro to evaluate 5 and 6 band resistors. The rest of the workbook is already setup to enter 4, 5, and 6 band resistors, it's just that the Evaluate macro is not setup for 5 and 6 band resistors and the current Evaluate macro is giving me problems. The code came straight out of the book. Any help is greatly appreciated.

The error I get is Compile Error: Case Without Select Case and the code is below.

Thanks,
Matt

Sub Evaluate()
'
' Evaluate Macro
'
Case 4
'4 bands
nLegalValue = 0
'preset value to indicate that the output is legal
'if set to 1 then this is an illegal value
If Range(“d7”).Value < 10 Then
nForward = Range(“d7”).Value * 10
If Range(“f7”).Value < 10 Then
nForward = nForward + Range(“f7”).Value
If FindValueA(nForward) = 1 Then
‘found Value
Else
nLegalValue = 1
End If
Else
'second digit gold or silver
nLegalValue = 1
End If
Else
nLegalValue = 1
'to indicate an illegal value
End If
If nLegalValue = 1 Then
Range(“result”).Value = “Forward value not found”
Else
If Range(“h7”).Value = 10 Then
'silver
nForward = nForward * 0.01 & “R”
Else
If Range(“h7”).Value = 11 Then
'gold
nForward = nForward * 0.1 & “R”
Else
'any other value
nForward = nForward * 10 ^ Range(“h7”).Value
If nForward < 1000 Then
nForward = nForward & “R”
Else
If nForward < 1000000 Then
nForward = nForward / 1000 & “K”
Else
nForward = nForward / 1000000 & “M”
End If
End If
End If
End If
'now for the tolerance
Select Case Range(“j7”).Value
Case 11:
'gold
Range(“result”).Value = nForward & “ 5%”
Case 10:
'silver
Range(“result”).Value = nForward & “ 10%”
Case 1:
'brown
Range(“result”).Value = nForward & “ 1%”
Case 2:
'red
Range(“result”).Value = nForward & “ 2%”
Case 5:
'green
Range(“result”).Value = nForward & “ 0.5%”
Case 6:
'blue
Range(“result”).Value = nForward & “ 0.25%”
Case 7:
'violet
Range(“result”).Value = nForward & “ 0.1%”
Case Else:
Range(“result”).Value = nForward & “ ??%”
End Select
End If
'now for reverse
nLegalValue = 0
If Range(“j7”).Value < 10 Then
nForward = Range(“j7”).Value * 10
If Range(“h7”).Value < 10 Then
nForward = nForward + Range(“h7”).Value
If FindValueA(nForward) = 1 Then
'found Value
Else
nLegalValue = 1
End If
Else
'second digit gold or silver
nLegalValue = 1
End If
Else
nLegalValue = 1
'to indicate an illegal value
End If
If nLegalValue = 1 Then
Range(“result2”).Value = “Reverse value not found”
Else
If Range(“f7”).Value = 10 Then
'silver
nForward = nForward * 0.01 & “R”
Else
If Range(“f7”).Value = 11 Then
'golde
nForward = nForward * 0.1 & “R%”
Else
'any other value
nForward = nForward * 10 ^ Range(“f7”).Value
If nForward < 1000 Then
nForward = nForward & “R”
Else
If nForward < 1000000 Then
nForward = nForward / 1000 & “K”
Else
nForward = nForward / 1000000 & “M”
End If
End If
End If
End If
'now for the tolerance
Select Case Range(“d7”).Value
Case 11:
'gold
Range(“result2”).Value = nForward & “ 5%”
Case 10:
'silver
Range(“result2”).Value = nForward & “ 10%”
Case 1:
'brown
Range(“result2”).Value = nForward & “ 1%”
Case 2:
'red
Range(“result2”).Value = nForward & “ 2%”
Case 5:
'green
Range(“result2”).Value = nForward & “ 0.5%”
Case 6:
'blue
Range(“result2”).Value = nForward & “ 0.25%”
Case 7:
'violet
Range(“result2”).Value = nForward & “ 0.1%”
Case Else:
Range(“result2”).Value = nForward & “ ??%”
End Select
End If
End Sub

Hi

Sub Evaluate()
'
' Evaluate Macro
'
Case 4
'4 bands
nLegalValue = 0

In this there is no Select Case

Change this as

Sub Evaluate()
'
' Evaluate Macro
'
Select Case Resistor
Case 4
'4 bands
nLegalValue = 0

But it may not work propery, because I just give an example, you should Replace Resistor for appropriate variable (Integer variable that represents Resistor.)

Thanks for the help, but I'm still not getting anywhere. I'm not sure which variable the integer relates to? I've tried some of the different key words in the Select Case statement, then I tried attaching .Value to the end of the statement and I'm still not getting anywhere.

Maybe if you saw the entire worksheet with the resistor toolbar with all of the macros you will see what is going on?

Thanks again!

This article has been dead for over six months. Start a new discussion instead.