Error on Assigning Function Output to Variable Excel VBA

Question

I am using a function that I copied from Microsoft that takes a column number and reassigns the column letters. I need to do this to create a formula. I have researched all day and cannot pinpoint the cause of my errors (I also tried to accomplish this as a sub proc). The function is in its own module. I tested it and it works fine:

Function ConvertToLetter(ByRef iCol As Integer) As String
  Dim iAlpha As Integer
  Dim iRemainder As Integer
  iAlpha = Int(iCol / 27)
  iRemainder = iCol - (iAlpha * 26)
  If iAlpha > 0 Then
     ConvertToLetter = Chr(iAlpha + 64)
  End If
  If iRemainder > 0 Then
     ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
  End If
End Function

The code that is giving me an error is:

For groups = 1 To i ' level 1 grouping
    For iCol = 24 To 136
       rCol = ConvertToLetter(iCol)
       Cells(Start(groups) - 1, rCol).Formula = "=COUNTA(" & rCol & Start(groups) & ":" & rCol & Finish(groups) & ")"
    Next
Next

I tried substituting the function into the formula itself:

  Cells(Start(groups) - 1, ConvertToLetter(iCol)).Formula = "=COUNTA(" & ConvertToLetter(iCol) & Start(groups) & ":" & ConvertToLetter(iCol) & Finish(groups) & ")"

The debugger made it past the first function call, but not the second & third. The errors I receive are types of "expecting a variable or procedure, not module." With the second case, I get other errors, and my head is so fuzzy, I cannot recall them.

Any help is greatly appreciated. I have run out of ideas. Thanks so much!

What are you using such a big function :) See this link It is a 1 line code http://stackoverflow.com/questions/10106465/excel-column-number-from-column-name/10107264#10107264 Reading the rest of the question now :) — Siddharth Rout, Apr 04 '13 at 19:07
When you debug the line `Cells(Start(groups) - 1, rCol).Formula...` What is the value of `Start(groups)` and what does `Start()` do? — Siddharth Rout, Apr 04 '13 at 19:10
`I am using a function that I copied from Microsoft` Can you please share the link? — Siddharth Rout, Apr 04 '13 at 19:24
@siddharthRout - the link is http://support.microsoft.com/kb/833402 . Whoever wrote that example should really look at my alternative solution... — Floris, Apr 05 '13 at 00:48

score 2 · Accepted Answer · answered Apr 04 '13 at 20:03

You almost never need to convert columns to letters. First consider using FormulaR1C1

For groups = 1 To i ' level 1 grouping
    For iCol = 24 To 136
        lLast = Finish(groups) - Start(groups) + 1
        Sheet1.Cells(Start(groups) - 1, iCol).FormulaR1C1 = _
            "=COUNTA(R[1]C:R[" & lLast & "]C)"
    Next iCol
Next groups

If you don't like R1C1, you can use Address more directly

For groups = 1 To i ' level 1 grouping
    For iCol = 24 To 136
        Set rStart = Sheet1.Cells(Start(groups), iCol)
        Set rEnd = Sheet1.Cells(Finish(groups), iCol)

        rStart.Offset(-1, 0).Formula = _
            "=COUNTA(" & rStart.Address & ":" & rEnd.Address & ")"

    Next iCol
Next groups

I am overwhelmed. Thank you so very, very, VERY much!!!!! It WORKED. And you saved me DAYS (because I literally spent days trying to figure this out). To answer other questions, the "groups" variable works with a start(groups) and end(groups) to group the spreadsheet into 3 level of groupings and keys off of a hidden column. It figures out where the groups start and end based upon a color code and content in the first few columns. Thank you all SO very much again. — Aerogal31, Apr 05 '13 at 13:00

Floris · Answer 2 · 2013-04-05T00:46:32.323

1

The following function does conversion of column number to letters: it takes advantage of the .Address method to simplify life a lot.

Function convertToLetter(colnum)
mycell = [A1].Offset(0, colnum - 1).Address
convertToLetter = Mid(mycell, 2, Len(mycell) - 3)
End Function

But now that I look at your code - when you use the Cells function, you should call it with numbers, not letters. So you have another problem!

Try the following:

For groups = 1 To i ' level 1 grouping
    For iCol = 24 To 136
       rCol = ConvertToLetter(iCol)
       Cells(Start(groups) - 1, iCol).Formula = "=COUNTA(" & rCol & Start(groups) & ":" & rCol & Finish(groups) & ")"
    Next
Next

Note I used iCol not rCol in the Cells(Start(groups)-1, iCol) = part of the statement. It may not be the only thing wrong...

edited Apr 05 '13 at 00:46

answered Apr 04 '13 at 19:21

Floris

45,857
6
70
122

Floris, thank you for your solution. As I indicated to Siddarth, although I decided to go with Dick's suggestion, when other folks check this posting, they will appreciate an accurate solution for converting column numbers to letters. Thank you again for your time and effort. I can appreciate elegant code, and yours is. – Aerogal31 Apr 05 '13 at 13:14
I appreciate the compliment. You should "accept" Dick's answer if that is the one that best answers your question - or accept mine if you think it's more useful for other people. Either way, clicking that little check mark is the way you tell future visitors of this page your opinion. You can also upvote alternative answers to show "this isn't a bad solution either". You need a minimum of 15 reputation for that. – Floris Apr 05 '13 at 13:23

score 1 · Answer 3 · answered Apr 04 '13 at 19:22

The function that you are using is faulty. Check this example

Sub Sample()
    Dim iCol As Integer
    For iCol = 131 To 134
        Debug.Print iCol; ConvertToLetter(iCol)
    Next iCol
End Sub


Function ConvertToLetter(ByRef iCol As Integer) As String
    Dim iAlpha As Integer
    Dim iRemainder As Integer
    iAlpha = Int(iCol / 27)
    iRemainder = iCol - (iAlpha * 26)
    If iAlpha > 0 Then
       ConvertToLetter = Chr(iAlpha + 64)
    End If
    If iRemainder > 0 Then
       ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
    End If
End Function

Output

131 D[
132 D\
133 D]
134 D^

Use this code which I picked up from the link that I mentioned.

rcol = Split(Cells(, iCol).Address, "$")(1)

instead of

rCol = ConvertToLetter(iCol)

When I need to convert, I will. Your solution not only is correct, but (from a coder's perspective - albeit one who is extremely "old" and has not coded professionally in probably 20 years), far more elegant. For now I'm going with Dick's solution. Thank you for posting because others who follow this link will find a better (i.e., correct) solution for converting column numbers to letters. — Aerogal31, Apr 05 '13 at 13:09

Error on Assigning Function Output to Variable Excel VBA

3 Answers3