Cannot run Visual Basic Macro because of syntax error

[sactogroove]

Just reviewed Mr. Excel's YouTube demo on building macros to change upper case to lower case, etc. Received response in title. I use 2007 Excel on a PC. How can I effect a syntax change to effect results?

 

[jeffreybrown]

Can you post the macro you used?

 

[sactogroove]

Jeff-
Here it is:

Application.Run "PERSONAL.XLSB!LowerSelection"
Range("A3:E4").Select
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Select

Sub UpperSelection()
For Each cell In Selection
If Not cell.HasFormula Then
cell.Value = UCase(cell.Value)
End If
Next cell
End Sub
Sub Macro1()
'Sub ProperSelection()
For Each cell In Selection
If Not cell.HasFormula Then
cell.Value = _
Application_
.WorksheetFunction_
.Proper (cell.Value)
End If
Next cell
End Sub

Thanks in advance.

 

[jeffreybrown]

First, both of these run as expected, but the difference in what you posted, you did not have the variables declared so added the Dim statements. I'm not saying that is the problem, but you should declare your variables.

Now second, what are you trying to do with all that first part before the Upper and Proper subs? What is its connection to the Subs?

Is this something you are going to have other people use? Because using your Personal.xlsb is only only your computer and therefore others would not be able to access that procedure. How about placing your procedures in a regular module.

Sub UpperSelection()
    Dim cell As Range
    For Each cell In Selection
        If Not cell.HasFormula Then
            cell.Value = UCase(cell.Value)
        End If
    Next cell
End Sub

Sub ProperSelection()
    Dim cell As Range
    For Each cell In Selection
        If Not cell.HasFormula Then
            cell.Value = Application.WorksheetFunction.Proper(cell.Value)
        End If
    Next cell
End Sub