PDA

View Full Version : [SOLVED] Macro works only in one workbook



mtrilce
11-25-2016, 11:40 AM
Dear Friends


I have "concatena" macro that works great in an excel workbook but does not work in another. The modules are the same and in both cases macros are enabled ... Why does this happen?


Function Concatena(Rango As Range)
Dim Concat As String
For Each Celda In Rango.Cells
If Celda = "" Then GoTo Salto
Concat = Concat & ", " & Celda.Value
Salto:
Next Celda
Concat = Right(Concat, Len(Concat) - 2)
Concatena = Concat
End Function

Kenneth Hobs
11-25-2016, 03:23 PM
You confused Excel by naming the Module the same as the Function. If you must do that, call it like this: =Concatena.Concatena(C5:C6)

There was a missing reference. You probably have it.

I do recommend option explicit as first line in all Modules, Sheets, Userforms, etc. You can add it by default in VBE's Options menu to require variable declaration. Compile from the Debug menu before a run to check syntax with that option set.

I did a small tweak in my routine like yours.

Option Explicit

Sub Test_Concat()
MsgBox Concat(Range("C5:C6"))
End Sub


Function Concat(r As Range) As String
Dim cat As String, c As Range
Application.Volatile False
For Each c In r
If c.Value = "" Then GoTo Salto
cat = cat & ", " & c.Value
Salto:
Next c
If Len(cat) = 0 Then Concat = ""
cat = Right(cat, Len(cat) - 2)
Concat = cat
End Function

mtrilce
11-26-2016, 08:02 AM
Thank you very much!!!!
I will try to follow your advice