Can't run macros from Excel 2003 in Excel2007

**bijan999** · 07-15-2010, 03:05 PM

Hi, I have a problem that I can't run a spreadsheet made in Excel 2003 with macros when I open it with Excel 2007. In all the cells it gives me errors. I already have enable all the macros and security options in 2007. Even the safe location to open it. Is there something new in 2007 that won't let the macro run? Here is the macro just in case:

Sub Desglosa()
Dim cuentas(100) As String
    cadena = ActiveCell.Value
    Sheets("Analiza Cuentas").Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    Range("B4").Value = "Nombre Cta"
    Range("C4").Value = "Descripción Cta"
    Range("D4").Value = "Movto Mes"
    Range("E4").Value = "Ac Anual a la Fecha"
    y = 1
    For x = 1 To Len(cadena)
        If Mid$(cadena, x, 1) <> "," Then cuentas(y) = cuentas(y) + Mid$(cadena, x, 1) Else y = y + 1
    Next x
    For x = 1 To y
        celda = Str$(x + 5)
        celda = Right$(celda, Len(celda) - 1)
        celdab = "B" + celda
        celdac = "C" + celda
        celdad = "D" + celda
        celdae = "E" + celda
        Range(celdac).Value = cuentas(x)
        Formula1 = "=nomcta(" + celdac + ")"
        Range(celdab).Formula = Formula1
        If Range(celdab).Value = "101" Then Range(celdab).Value = "Cta. Inexistente"
        Formula2 = "=salmul(" + celdac + ",month(fecha),year(fecha))"
        Range(celdad).Formula = Formula2
        Formula3 = "=salacmul(" + celdac + ",month(fecha),year(fecha))"
        Range(celdae).Formula = Formula3
    Next x
    Range("A1").Select
End Sub

Thanks

**romperstomper** · 07-15-2010, 03:11 PM

Hi and welcome to the forum.

In future, when posting code, please include it in code tags (I have done it for you this time)

Are you by chance getting #NAME errors?

**bijan999** · 07-15-2010, 03:45 PM

Hi, thanks for the advise. Yes, I'm getting that kind of erros "#NAME". Any thoughts?

**romperstomper** · 07-15-2010, 03:46 PM

The functions you are using (nomcta, salmul and salacmul) are not built-in functions - they are written in code and I would suspect that they are located in an add-in which you do not have installed in your 2007 setup.

**bijan999** · 07-15-2010, 04:31 PM

Hi Romperstomper, checking Microsoft Visual Basic in Excel, I found this other code. (this Excel worksheet was made by a former worker and he already left the company)

Declare Function CON_conexion Lib "konoff97.dll" _
          (ByVal clavehotel As String, _
           ByVal Usr As String, _
           ByVal Pwd As String, _
           ByVal Server As String, _
           ByVal mes As String, _
           ByVal Ano As String, _
           ByVal Emp As String) _
     As Integer
  
Declare Function CON_WildCuentas Lib "konoff97.dll" _
          (ByVal TpoInfo As Integer, _
           ByVal clavehotel As String, _
           ByVal EjercioFiscal As Integer, _
           ByVal CuentaContable As String, _
           ByVal MesInicial As Integer, _
           ByVal MesFinal As Integer, _
              resultado As Double) _
     As Integer
     
Declare Function CON_WildCuentasDep Lib "konoff97.dll" _
          (ByVal TpoInfo As Integer, _
           ByVal clavehotel As String, _
           ByVal EjercioFiscal As Integer, _
           ByVal CuentaContable As String, _
           ByVal Departamento As String, _
           ByVal MesInicial As Integer, _
           ByVal MesFinal As Integer, _
              resultado As Double) _
     As Integer

     
Declare Function CON_NomCuenta Lib "konoff97.dll" _
          (ByVal clavehotel As String, _
           ByVal CuentaContable As String, _
           ByVal CNomCta As String) _
     As Integer
     
Declare Function CON_NumCuentas Lib "konoff97.dll" _
          (ByVal clavehotel As String, _
           ByVal CuentaContable As String, _
           NumCtas As Integer) _
     As Integer



Const TCAR = 1
Const TABO = 2
Const TSAL = 3
Const TSALINI = 4
Const TCARAC = 5
Const TABOAC = 6
Const TSALAC = 7
Const TPRE = 8
Const TSALP = 9
Const TSALACP = 10
Const clavehotel = "PVR"


Function CAR(CuentaContable As String, _
            ByVal MesInicial As Integer, Optional ByVal MesFinal As Variant, _
             Optional ejerciciofiscal As Variant)
Dim st As Integer, resultado As Double
If IsMissing(ejerciciofiscal) Then ejerciciofiscal = -1
If IsMissing(MesFinal) Then MesFinal = MesInicial
st = CON_WildCuentas(TCAR, clavehotel, ejerciciofiscal, CuentaContable, MesInicial, MesFinal, resultado)
If st <> 0 Then
   CAR = CVErr(xlErrNum)
Else
   CAR = resultado
End If
End Function

Function ABO(CuentaContable As String, _
            ByVal MesInicial As Integer, Optional ByVal MesFinal As Variant, _
             Optional ejerciciofiscal As Variant)
Dim st As Integer, resultado As Double
If IsMissing(ejerciciofiscal) Then ejerciciofiscal = -1
If IsMissing(MesFinal) Then MesFinal = MesInicial
st = CON_WildCuentas(TABO, clavehotel, ejerciciofiscal, CuentaContable, MesInicial, MesFinal, resultado)
If st <> 0 Then
   ABO = CVErr(xlErrNum)
Else
   ABO = resultado
End If
End Function

Function SAL(CuentaContable As String, _
            ByVal MesInicial As Integer, Optional ByVal MesFinal As Variant, _
             Optional ejerciciofiscal As Variant)
Dim st As Integer, resultado As Double
If IsMissing(ejerciciofiscal) Then ejerciciofiscal = -1
If IsMissing(MesFinal) Then MesFinal = MesInicial
st = CON_WildCuentas(TSAL, clavehotel, ejerciciofiscal, CuentaContable, MesInicial, MesFinal, resultado)
If st <> 0 Then
   SAL = CVErr(xlErrNum)
Else
   SAL = resultado
End If
End Function

Function SALDEP(Departamento As String, _
            CuentaContable As String, _
            ByVal MesInicial As Integer, Optional ByVal MesFinal As Variant, _
             Optional ejerciciofiscal As Variant)
Dim st As Integer, resultado As Double
If IsMissing(ejerciciofiscal) Then ejerciciofiscal = -1
If IsMissing(MesFinal) Then MesFinal = MesInicial
st = CON_WildCuentasDep(TSAL, clavehotel, ejerciciofiscal, CuentaContable, Departamento, MesInicial, MesFinal, resultado)
If st <> 0 Then
   SALDEP = CVErr(xlErrNum)
Else
   SALDEP = resultado
End If
End Function


Function SALINI(CuentaContable As String, _
                ByVal MesInicial As Integer, Optional ByVal MesFinal As Variant, _
                 Optional ejerciciofiscal As Variant)
Dim st As Integer, resultado As Double
If IsMissing(ejerciciofiscal) Then ejerciciofiscal = -1
If IsMissing(MesFinal) Then MesFinal = MesInicial
st = CON_WildCuentas(TSALINI, clavehotel, ejerciciofiscal, CuentaContable, MesInicial, MesFinal, resultado)
If st <> 0 Then
   SALINI = CVErr(xlErrNum)
Else
   SALINI = resultado
End If
End Function


Function PRE(CuentaContable As String, _
            ByVal MesInicial As Integer, Optional ByVal MesFinal As Variant, _
             Optional ejerciciofiscal As Variant)
Dim st As Integer, resultado As Double

If IsMissing(ejerciciofiscal) Then ejerciciofiscal = -1
If IsMissing(MesFinal) Then MesFinal = MesInicial
st = CON_WildCuentas(TPRE, clavehotel, ejerciciofiscal, CuentaContable, MesInicial, MesFinal, resultado)
If st <> 0 Then
   PRE = CVErr(xlErrNum)
Else
   PRE = resultado
End If
End Function

Function CARAC(CuentaContable As String, _
                ByVal mes As Integer, _
                 Optional ejerciciofiscal As Variant)
Dim st As Integer, resultado As Double
If IsMissing(ejerciciofiscal) Then ejerciciofiscal = -1
st = CON_WildCuentas(TCARAC, clavehotel, ejerciciofiscal, CuentaContable, mes, mes, resultado)
If st <> 0 Then
   CARAC = CVErr(xlErrNum)
Else
   CARAC = resultado
End If
End Function

Function ABOAC(CuentaContable As String, _
                ByVal mes As Integer, _
                 Optional ejerciciofiscal As Integer)
Dim st As Integer, resultado As Double
If IsMissing(ejerciciofiscal) Then ejerciciofiscal = -1
st = CON_WildCuentas(TABOAC, clavehotel, ejerciciofiscal, CuentaContable, mes, mes, resultado)
If st <> 0 Then
   ABOAC = CVErr(xlErrNum)
Else
   ABOAC = resultado
End If
End Function


Function NOMCTA(ByVal CuentaContable As String)
Dim st As Integer, CNomCta As String * 100
st = CON_NomCuenta(clavehotel, CuentaContable, CNomCta)
If st <> 0 Then
   NOMCTA = st
   ' CVErr(xlErrNum)
Else
   NOMCTA = CNomCta
End If
End Function

Function SALACDEP(Departamento As String, _
                  CuentaContable As String, _
                  ByVal mes As Integer, _
                   Optional ejerciciofiscal As Variant)
Dim st As Integer, resultado As Double
If IsMissing(Departamento) Then Departamento = ""
If IsMissing(ejerciciofiscal) Then ejerciciofiscal = -1
st = CON_WildCuentasDep(TSALAC, clavehotel, ejerciciofiscal, CuentaContable, Departamento, mes, mes, resultado)
If st <> 0 Then
   SALACDEP = CVErr(xlErrNum)
Else
   SALACDEP = resultado
End If
End Function


Function SALAC(CuentaContable As String, _
                  ByVal mes As Integer, _
                   Optional ejerciciofiscal As Variant)
Dim st As Integer, resultado As Double
If IsMissing(ejerciciofiscal) Then ejerciciofiscal = -1
st = CON_WildCuentas(TSALAC, clavehotel, ejerciciofiscal, CuentaContable, mes, mes, resultado)
If st <> 0 Then
   SALAC = CVErr(xlErrNum)
Else
   SALAC = resultado
End If
End Function

Function SALACP(CuentaContable As String, _
                  ByVal mes As Integer, _
                   Optional ejerciciofiscal As Variant)
Dim st As Integer, resultado As Double
If IsMissing(ejerciciofiscal) Then ejerciciofiscal = -1
st = CON_WildCuentas(TSALACP, clavehotel, ejerciciofiscal, CuentaContable, mes, mes, resultado)
If st <> 0 Then
   SALACP = CVErr(xlErrNum)
Else
   SALACP = resultado
End If
End Function


Function SALP(CuentaContable As String, _
                  ByVal mes As Integer, _
                   Optional ejerciciofiscal As Variant)
Dim st As Integer, resultado As Double
If IsMissing(ejerciciofiscal) Then ejerciciofiscal = -1
st = CON_WildCuentas(TSALP, clavehotel, ejerciciofiscal, CuentaContable, mes, mes, resultado)
If st <> 0 Then
   SALP = CVErr(xlErrNum)
Else
   SALP = resultado
End If
End Function

Function NumCtas(CuentaContable As String, Optional clavehotel As Variant)
Dim st As Integer, NCtas As Integer
st = CON_NumCuentas(clavehotel, CuentaContable, NCtas)
If st <> 0 Then
   NumCtas = CVErr(xlErrNum)
Else
   NumCtas = NCtas
End If
End Function

Function SalacMul(cadena As String, ByVal mes As Integer, Optional ejerciciofiscal As Variant)
Dim cuentas(60) As String, resultado As Double, acumulado As Double
acumulado = 0
x = 1
For y = 1 To Len(cadena)
    If Mid$(cadena, y, 1) <> "," Then cuentas(x) = cuentas(x) + Mid$(cadena, y, 1) Else x = x + 1
Next y
For y = 1 To x
   If Left$(cuentas(y), 1) <> "-" Then acumulado = acumulado + SALAC(cuentas(y), mes, ejerciciofiscal) Else acumulado = acumulado - SALAC(Right$(cuentas(y), Len(cuentas(y)) - 1), mes, ejerciciofiscal)
Next y
SalacMul = acumulado
End Function

Function SalMul(cadena As String, ByVal mes As Integer, Optional ejerciciofiscal As Variant)
Dim cuentas(60) As String, resultado As Double, acumulado As Double
acumulado = 0
x = 1
For y = 1 To Len(cadena)
    If Mid$(cadena, y, 1) <> "," Then cuentas(x) = cuentas(x) + Mid$(cadena, y, 1) Else x = x + 1
Next y
For y = 1 To x
   If Left$(cuentas(y), 1) <> "-" Then acumulado = acumulado + SAL(cuentas(y), mes, mes, ejerciciofiscal) Else acumulado = acumulado - SAL(Right$(cuentas(y), Len(cuentas(y)) - 1), mes, mes, ejerciciofiscal)
Next y
SalMul = acumulado
End Function

Function CONECTA(clavehotel As String, Usr As String, Pwd As String, Server As String, mes As String, Ano As String, Emp As String)
st = CON_conexion(clavehotel, Usr, Pwd, Server, mes, Ano, Emp)
If st <> 0 Then
   CONECTA = CVErr(xlErrNum)
Else
   CONECTA = 0
End If
End Function

**romperstomper** · 07-15-2010, 04:37 PM

Can you post the workbook that doesn't work?

**bijan999** · 07-15-2010, 04:50 PM

Originally Posted by romperstomper

Can you post the workbook that doesn't work?

Yes, I can, but the problem is that it has a link to the SQL server in my company. That's what the second code refers to.

**romperstomper** · 07-15-2010, 04:52 PM

That should not be relevant to the #NAME error, so it shouldn't matter.

**bijan999** · 07-15-2010, 05:18 PM

Originally Posted by romperstomper

That should not be relevant to the #NAME error, so it shouldn't matter.

I'll try to upload it as soon as possible. There seems to be a problem with the site. (The Excel Help Forum database has encountered a problem).

Thanks.

**bijan999** · 07-16-2010, 10:59 AM

Originally Posted by bijan999

I'll try to upload it as soon as possible. There seems to be a problem with the site. (The Excel Help Forum database has encountered a problem).

Thanks.

Here is the file. You will have to rename it to .rar because I couldn't compress it with winzip belowe 1 mb. Thanks.