Invalid outside procedure

**BRISBANEBOB** · 10-25-2010, 08:14 PM

I'm getting the compile error 'Invalid Outside Procedure' when I'm attempting to run the code below; can anyone help me to correct this? Absolute newbie at code...

'

Function ExcelCol(ByVal intCol As Long) As String
' Columns can be any number from 1 to x
' Will give valid data up to Column ZZ 
' ASCII A = 65 so since we're starting with 1
' chr(intCol + 64 ) = A
' Subtract 1 for because intCol = 1 = 'A'
    
Dim intRemainder As Integer
Dim intMod As Integer
Dim intColumn

    If intCol <= 26 Then
        ExcelCol = Chr(intCol + 64)
    Else
        intRemainder = intCol Mod 26
        intMod = intCol \ 26
        
        If intRemainder = 0 Then
            ExcelCol = ExcelCol(intMod - 1) & "Z"
        Else
            ExcelCol = ExcelCol(intMod) & Chr(intRemainder + 64)
        End If
    End If
End Function

**Leith Ross** · 10-25-2010, 09:08 PM

Hello Bob,

The function works fine for me. As a matter of interest, the macro below does the same thing. It works with Excel 2000 and up.

Function ExcelCol(ByVal intCol As Long) As String
    ExcelCol = Split(Columns(intCol).Address(False, False), ":")(0)
End Function

**BRISBANEBOB** · 10-25-2010, 09:16 PM

HI Leith

Thanks for your response. The code I am using still does not work so I am not sure if there is some setting issue with my Excel 2007. Reference your code - which part of my code does it replace (I warned you I was a newbie...)

Thanks

**BRISBANEBOB** · 10-25-2010, 09:44 PM

Leith

I am trying to run a version control so the whole thing looks like this:

'Function ExcelCol(ByVal intCol As Long) As String
' Columns can be any number from 1 to x
' Will give valid data up to Column ZZ (If you have more than 26^2 versions, you have bigger problems than this)
' ASCII A = 65 so since we're starting with 1
' chr(intCol + 64 ) = A
' Subtract 1 for because intCol = 1 = 'A'
    
Dim intRemainder As Integer
Dim intMod As Integer
Dim intColumn

    If intCol <= 26 Then
        ExcelCol = Chr(intCol + 64)
    Else
        intRemainder = intCol Mod 26
        intMod = intCol \ 26
        
        If intRemainder = 0 Then
            ExcelCol = ExcelCol(intMod - 1) & "Z"
        Else
            ExcelCol = ExcelCol(intMod) & Chr(intRemainder + 64)
        End If
    End If
End Function
Sub SaveNextVersion()
  Dim FName As String
  Dim FPath As String
  Dim mySheet As Worksheet
  
  Set mySheet = ThisWorkbook.ActiveSheet
  
  'Capture Information for next versions
  FName = mySheet.Range("A3").Text & " " & mySheet.Range("B3").Text & " " & mySheet.Range("C3").Text
  FPath = Range("H4").Value
      
  'Update so that correct version information saved
  mySheet.Range("A2") = mySheet.Range("A3").Value
  mySheet.Range("B2") = mySheet.Range("B3").Value
  mySheet.Range("C2") = mySheet.Range("C3").Value
  
  'Save File as new Version
  ThisWorkbook.SaveAs Filename:=FPath & "\" & FName
End Sub

**DonkeyOte** · 10-26-2010, 02:56 AM

Without reviewing the code re: optimisation...

You might want to remove the apostrophe on line 1
Given the Function Header does not exist (commented out) the subsequent code lines are invalid / sitting outside of a procedure.