lunes, 4 de noviembre de 2013

VBA: Las dobles comillas en las cadenas de programación VBA para Excel.

Recientemente una lectora consultaba cómo podía solucionar un problema para incluir en una .Formula un entrecomillado. Esto me hizo recordar un par de formas habituales para combatir esta situación.
En particular la lectora preguntaba por la forma de escribir la siguiente instrucción para introducir una fórmula, mediante programación, en una celda, empleando la propiedad .Formula:
Sub formula()
Sheets("Datos").Select
Range("e2").formula = "=SI(A2="","",SI(B2="x",0,SI(C2="",0,Datos!$D$2)))"
End Sub

Escrito así el depurador se dentendría en las primeras comillas...
¿Cuál es entonces la manera correcta de saltar este inconveniente???.


La primera y más sencilla es emplear en método de doble comillado, cambiando las comillas simples por unas dobles. En el ejemplo se vé mejor:

Sub formula1()

Sheets("Datos").Range("E2").FormulaLocal = _
    "=SI(A2="""";"""";SI(B2=""x"";0;SI(C2="""";0;Datos!$D$2)))"

End Sub



pero este método podría generar algún fallo, por lo que la alternativa sería usar el caracter 34 (Chr(34) que representa las dobles comillas), pero con igual forma de trabajo:

Sub formula2()

Sheets("Datos").Range("E3").FormulaLocal = _
    "=SI(A2=" & Chr(34) & Chr(34) & ";" & Chr(34) & Chr(34) & ";SI(B2=" & Chr(34) & "x" & Chr(34) & ";0;SI(C2=" & Chr(34) & Chr(34) & ";0;Datos!$D$2)))"

End Sub



Una manera adicional, algo más cómoda que la anterior, sería construir una función personalizada que replique la contrucción de esa parte del doble comillado. La UDF sería:

Function comillas(Texto As String) As String
  comillas = Chr(34) & Texto & Chr(34)
End Function

y el código de la fórmula sería entonces:

Sub formula3()

Sheets("Datos").Range("E4").FormulaLocal = _
    "=SI(A2=" & comillas("") & ";" & comillas("") & ";SI(B2=" & comillas("x") & ";0;SI(C2=" & comillas("") & ";0;Datos!$D$2)))"
End Sub



La idea de las dobles comillas es ir acotando secciones de la cadena de texto que componemos con la fórmula y 'variables' que concatenamos con el resto

Nota: el caracter de comilla simple es Chr(39).

25 comentarios:

  1. hola. necesito poder realizar este calculo y no logro encontrar una formula.

    si B5=1 B6=S6 , si B5=2 B6=T6 y si B5 no es 1 ni 2 que B6 sea 0

    me ayudan?

    gracias

    ResponderEliminar
    Respuestas
    1. =SI(B5=1,"S6",SI(B5=2,"T6",0))

      Eliminar
    2. coloca esta formula en la columna B6 =SI(B5=1,"S6",SI(B5=2,"T6",0))

      Eliminar
  2. con un código de VB colócalo dentro de la hoja donde se realizara el calculo. y cada cambies B5 se actualizara el valor de B6

    Sub formula()


    Range("B6").FormulaLocal = _
    "=SI(B5=1,""S6"",SI(B5=2,""T6"",0))"


    End Sub

    ResponderEliminar
    Respuestas
    1. Gracias por los aportes,
      como ves Horacio son respuestas iguales a las que te comenté por G+

      Un cordial saludo a todos!

      Eliminar
  3. HOLA ESTIMADOS, TENGO UN PROBLEMA, AL HACER ESTA FORMULA LOCAL EN VBA: SI.ERROR(SI(Y(D2>0,D2=AH2,F2=AI2,AH2>0),SUMA(AV2),""),0)

    Cells(Target.Row, 10).FormulaLocal = "=SI.ERROR(SI(Y(D" & CStr(Target.Row) & ">0, D" & CStr(Target.Row) & "= AH" & CStr(Target.Row) & ", F" & CStr(Target.Row) & "= AI" & CStr(Target.Row) & ", AH" & CStr(Target.Row) & ">0),SUMA(AV" & CStr(Target.Row) & ",""""),0)"

    '

    ResponderEliminar
    Respuestas
    1. Hola Juan César,
      prueba sin convertir el número de fila en Texto (sin la función Cstr), y evita los espacios:
      ", F"
      mejor
      ",F"
      en vez "= AI" mejor "=AI"
      etc.
      Saludos

      Eliminar
  4. HOLA ISMAEL, ME SALIO BIEN . ASI COMO ME HAS DICHO :
    Cells(Target.Row, 22).FormulaLocal = "=SI.ERROR(SI(Y(D2>0,D2=AH2,F2=AI2,AH2>0),SUMA(AV2),""""),0)"

    PERO HAY UN DETALLE: QUE SOLO ME FUNCIONA CON LA FILA 2. ES POR ESO QUE EMPLEO Cstr , PARA QUE ASI HAGA UN TRABAJO EN CUALQUIER FILA DE LA COLUMNA 22. EL PROBLEMA ES QUE NO ME SALE BIEN .

    ResponderEliminar
    Respuestas
    1. Me refería a que dejaras la variable de fila, pero sin la función CStr:
      algo así:
      Cells(Target.Row, 10).FormulaLocal = "=SI.ERROR(SI(Y(D" & (Target.Row) & ">0, D" & (Target.Row) & etc etc
      Saludos

      Eliminar
  5. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  6. Hola Ismael, deje la funcion CStr y nada que funciona :

    no sera algun problema de comillas, alli te lo vuelvo a enviar :


    = SI.ERROR(SI(Y(D2>0),SUMA(AV2),""),0)

    Cells(Target.Row, 22).FormulaLocal = "=SI.ERROR(SI(Y(D" & (Target.Row) & ">0),SUMA(AV" & (Target.Row) & ",""""),0)"

    -----------------------------------------------------------------------------------------------------------------------
    Esta es otra formula simple y no tuve problemas con la formula local en vba:
    =SI.ERROR(I2/I2,0)

    Cells(Target.Row, 10).FormulaLocal = "=SI.ERROR(I" & CStr(Target.Row) & "/I" & CStr(Target.Row) & ",0)"

    ResponderEliminar
    Respuestas
    1. Hola Juan Cesar,
      Realmente el fallo lo tienes en que te falta cerrar el paréntesis de la SUMA, sería algo así:
      "=SI.ERROR(SI(Y(D" & Target..Row & ">0);SUMA(AV" & Target.Row & ");"""");0)"

      Saludos

      Eliminar
  7. EXCELENTE ISMAEL,

    ME SALIO . MUCHISIMA GRACIAS !!! HERMANO

    ResponderEliminar
  8. Buen Día.

    ojala y puedan ayudarme con una duda en el uso del select case, tengo una celda en Excel A8 de tipo texto la cual su valor es: "1001" To "101009", "101012".
    Requiero usar esta celda como lista de expresiones del select case:
    Select case cuenta
    case A8
    acciones a realizar
    case A9 --> donde A9 tiene otro valor

    y así sucesivamente. El objetivo es crear un balance general donde en la columna A tengo los criterios para seleccionar que cuentas componen ese Rubro tomando los valores de un arreglo con todos los movimientos del mes de cada cuenta contable.
    anexo parte del código que estoy usando

    For f = 8 To 40

    Select Case MatrizD(r, 1)
    Case Worksheets("Balance General 2016").Cells(f, 1)
    Call Llena(Worksheets("Balance General 2016").Cells(f, 30).Value)
    End Select
    Next f

    ResponderEliminar
    Respuestas
    1. Hola Joel,
      tendría que ver el código en su contexto..
      Si puedes envíame el fichero a
      excelforo@gmail.com

      Slds

      Eliminar
  9. ya envié el fichero.

    la consulta va enfocada, a si es posible en la expresión del case usar una celda donde esta la condición, en este caso la columna A donde contendrá las condiciones.

    en lugar de usar directamente las condiciones ej.

    Select Case MatrizD(r, 1)
    Case "100" To "101999999"
    acción a realizar
    Case "105" To "105999999"
    acción a realizar
    usar

    Select Case MatrizD(r, 1)
    Case Worksheets("Balance General 2016").Cells(8, 1).Value
    acción a realizar
    Case Worksheets("Balance General 2016").Cells(9, 1).Value
    ...
    donde en la celda estará la condición.

    slds.

    ResponderEliminar
  10. Excelente aporte muchas gracias
    Alejandro

    ResponderEliminar
  11. me parecio excelentetu post, pero no logre solucionar mi problema con las comillas, te dejo mi formula en vba

    Worksheets("productos").Range("N3").Formula = "=SI(E3=" & Chr(34) & Chr(34) & ";" & Chr(34) & Chr(34) & ";SI(ESNOD(BUSCARV($E3;categorias!$C$1:$G$1000001;3;0));" & Chr(34) & "-" & Chr(34) & ";BUSCARV($E3;categorias!$C$1:$G$1000001;3;0)))"

    Cuando la pongo a funcionar manda un error Que dice: "Error definido por la aplicación"

    Pero si paso la cadena a un textbox me muestra la fomula, entonces la copio y la pego en la celda y funciona perfecto.

    ResponderEliminar
    Respuestas
    1. Hola Edwin,
      si empleas .range.formula debes usar las funciones en inglés...
      si las vas a utilizar en español, emplea
      .range.formulalocal

      saludos

      Eliminar
    2. Gracias. me funcionó. ni pensar que me la pasé todo el domingo buscando el error :)

      Eliminar
  12. Hola buen día, tengo un problema con mi código:

    Cells(5, 5).Value = "=SI.ERROR(IMPORTARDATOSDINAMICOS(""Monto"",Área2!$A$3,""MonthModified"",E$2,""Nombre cuenta"",$C4),0)"

    La macro (la cual lo único que quiero es que la celda tenga una referencia a una tabla dinámica de otra hoja) corre sin problemas, sin embargo en la celda aparece #¿NOMBRE? y esto se soluciona cuando le doy doble click a la celda, pero de otro modo no se cambia; AYUDA por favor.

    ResponderEliminar
    Respuestas
    1. Hola,
      si quieres añadir una fórmula debes emplear la propiedad:
      .Formula
      .FormulaLocal
      .FormulaR1C1

      En tu caso:
      Cells(5, 5).FormulaLocal = "=SI.ERROR(IMPORTARDATOSDINAMICOS(""Monto"",Área2!$A$3,""MonthModified"",E$2,""Nombre cuenta"",$C4),0)"

      Ojo con las comas o punta y comas... se ajustarán a tu configuración de Windows

      Saludos

      Eliminar
  13. Muchas gracias, el problema se solucionó, eres un genio.

    ResponderEliminar

Nota: solo los miembros de este blog pueden publicar comentarios.