Formularios en vbasic para excel.

Según wikipedia:

Un formulario es un documento, ya sea físico o digital, diseñado para que el usuario introduzca datos estructurados (nombres, apellidos, dirección, etc.) en las zonas correspondientes, para ser almacenados y procesados posteriormente. Esto ayuda a que diferentes instancias, registren datos personales de la persona que los llena para posteriormente ser acreedor al servicio solicitado, siempre y cuando, los datos sean llenados correctamente.

En informática, un formulario consta de un conjunto de Campos de datos solicitados por un determinado programa, los cuales se almacenarán para su procesamiento y posterior uso. Cada campo debe albergar un dato específico, por ejemplo, el campo "Nombre" debe rellenarse con un nombre personal; el campo "Fecha de nacimiento" debe aceptar una fecha válida, etc.

En vbasic para excel

 Formulario Un formulario es una ventana del sistema operativo Windows. Este formulario es la interfase gráfica de su aplicación, sobre el que podrá añadir los controles que necesite su programa. Podemos abrir tantas ventanas como queramos en nuestro proyecto, pero el nombre de cada una de ellas debe ser distinto. Por defecto la ventana que se abre en un proyecto Visual Basic tiene el nombre de Form1.

Evento Un evento es una acción que sucede en un objeto, decimos también que es un proceso que ocurre en un momento no determinado causando una respuesta por parte de un objeto. Los objetos están atentos a cualquier evento que ocurra en u entorno o dentro de ellos mismos. Un programa Visual Basic es un POE (Programa orientado a eventos). Es decir, cuando se mueve el mouse por la pantalla, se escribe algún texto, etc.; nuestro programa está atento a que algún evento ocurra, en qué objeto ocurre y que acción debe tomar (programa)

• Es un objeto que contiene objetos.
• Como todo objeto tiene propiedades.
• Como todo objeto puede verse sometido a eventos.

Un formulario es un contenedor de objetos (controles). 

Propiedades que yo particularmente he utilizado mas en un formulario.

• Caption : Es el titulo que queremos que aparezca cuando se presente el formulario. Normalmente se fija en el momento de crear el formulario aunque se puede modificar por programa.

• Height y width: Alto y ancho. Normalmente se fijan en el momento de crear el formulario. Se puede modificar por programa.
• Name: Nombre del formulario. Se fija en el momento de creación del formulario. Se puede modificar editando el formulario. Es el nombre con el que se llama al formulario en vbasic.
• Hay varias propiedades mas, desde el color de fondo al tipo de letra que se pueden fijar en el momento de la creación o por programa.

Controles que se pueden incluir en un formulario:

• Etiqueta (label): Es un literal, se suele utilizar para indicar que es cada campo. Se puede fijar durante la creación del formulario y variarlo por programa. 
• Cuadro de texto (TextBox): Se utiliza para introducir textos. Se puede fijar durante su creación o por programa. 
• Cuadros combinados (Combobox) : Se utilizan para seleccionar un  opción de una lista de opciones.  Ademas de las propiedades comunes ya indicadas para los controles aparece la propiedad RowSource, que es el rango donde están  los datos a desplegar.
• Cuadros de lista (Listbox) : Se utilizan para seleccionar un  opción de una lista de opciones.  Ademas de las propiedades comunes ya indicadas para los controles aparece la propiedad RowSource, que es el rango donde están  los datos a desplegar.
• Casilla o checkbox: Permite validar o no un determinado campo.
• Botones de opción y marco: Permite seleccionar entre varias una sola opción. El marco no es necesario si solo hay un grupo de opciones. En caso de tener mas de un grupo de opciones un marco debe rodear a cada grupo.

Botones de opción rodeados por un marco, etiquetas, cuadro de texto y botones de comando

• Botón de comando (CommandButton). Se suelen utilizar para opciones tales como "Aceptar" o "Cancelar". Emula un botón pulsable.
• Barra de desplazamiento (scrollbar): Permite variar un valor, mediante el desplazamiento del botón incorporado en la barra, entre dos valores límites. Este incremento es un parámetro programable de estos controles.
• Botón de número (SpinButton):Muy parecido a la barra de desplazamiento pero sin el botón desplazable central.

Propiedades comunes de los controles. Las mas comunes: 

• Name: Nombre del control. Se fija durante el proceso de creación del formulario. Una vez fijado, en programación, se hace referencia al control con ese nombre.
• Caption: Literal que leerá el usuario.
• Visible: true o false. Indica si el control se ve o no.
• Enabled: Con enabled=true el control se puede utilizar normalmente. Con enabled=false el control se ve pero no está operativo.

Formulario anterior con algunos controles con visible=false y el botón Iniciar con enabled=false 

• Left, Top, Height y width, izquierda, arriba, alto y ancho. Se fijan durante la creación del formulario. Se pueden variar por programa.
• Text: Texto incluido.
• Value: Valor del control.
• Hay varias propiedades comunes mas, desde el tipo de letra o al tipo de cursor que queremos que se vea al pasar el ratón sobre el control. 

Creando un formulario. Pueden verse varios controles ya añadidos, el cuadro de herramientas y la ventana de propiedades 

Crear y usar un formulario: Con Alt+F11 se puede entrar o ver las macros programadas. 

• Vamos a insertar, insertamos userform.
• El  cuadro de herramientas y la ventana de propiedades se activan o desactivan desde el menú ver. Si no los vemos, los activamos.
• Dimensionamos el formulario bien con el ratón, bien entrando en las propiedades y modificando los valores de hight y/o width.
• En propiedades buscamos name y ponemos, si no nos vale el nombre por omisión, un nombre adecuado a nuestro proyecto.
• En propiedades buscamos caption y ponemos el titulo que deseemos tenga el formulario al desplegarse.
• Vamos al cuadro de herramientas. Salvo los botones de opción ,que van agrupados, cada uno de los controles es independiente.
• Incluimos los controles que necesitemos. Cada control tiene sus propiedades pero, como ya he dicho, muchas son comunes. Con hight, width, top y left o arrastrando y dimensionando con el ratón podemos situarlos en el formulario.
• En nuestras macros debemos tener una que nos presente el formulario. Como ya he comentado el formulario y cada uno de los controles tiene un nombre, bien el que toma por omisión o bien el que le hayamos dado. 
• Llamo a mi formulario de prueba Formulario. Incluyo un botón "Aceptar" y un botón "Cancelar". Llamo a uno, en name,  aceptar y al otro, en un alarde de imaginación, cancelar.
• A partir de ese momento cada vez, en programación, que me refiera al formulario escribiré Formulario y cada vez que me refiera al botón aceptar escribiré Formulario.aceptar.
• Antes de presentar el formulario podemos o debemos variar algunos parámetros de los controles. Podemos incluir algún valor inicial, dejar invisible o no operable algún control hasta que otro evento los haga visibles u operables.

Código:

Sub VerFormulario()

With Formulario 'Nombre del formulario

.MedT = False     'Botones de opción iniciamos los dos a false. Sin selección

.MedVal = False

.TPeriodo.Text = 60 ' Valor inicial de TPeriodo

.Aceptar.Enabled = False 'Aceptar se ve pero no está operativo.

.Periodo.Visible = False ' Las dos etiquetas y el cuadro de texto no aparecen, estan ocultas

.TPeriodo.Visible = False

.Segundos.Visible = False

.Show ' Muestra formulario

End With

End Sub

Acciones al cambiar o seleccionar un control. Acciones al producirse un evento sobre un control:

• Desde el editor de formularios, con el formulario seleccionado, al hacer doble click sobre un control entramos en la macro que gestiona el evento mas común de ese control.

Código:

**************************************************

Private Sub MedT_Click()

With Formulario

.Aceptar.Enabled = True 'Aceptar hasta el momento no estaba operativo, no se había selecciona ninguna opción. Ya hay una opción, debe estar operativo.

.Periodo.Visible = True ' Los controles no visibles pasan a visibles para esta opción

.Segundos.Visible = True

.TPeriodo.Visible = True

End With

End Sub

*********************************************************************
Private Sub MedVal_Click()
With Formulario
.Aceptar.Enabled = True 'Aceptar hasta el momento no estaba operativo, no se había selecciona ninguna opción. Ya hay una opción, debe estar operativo.
' Estos controles  pasan a no visibles para esta opción
.Periodo.Visible = False
.Segundos.Visible = False
.TPeriodo.Visible = False
End With

End Sub
*********************************************************************
Private Sub Aceptar_Click()
Dim Cad, PE, PT, PV, Aux

Set Aux = Sheets("Aux")
Aux.Range("i3") = ""
With Formulario
.Hide 'Cierra el formulario
'Recupera los distintos valores del formulario, prepara lo que sea necesario y continua con el proceso.
PE = .TPeriodo.Value
PT = .MedT.Value
PV = .MedVal.Value
TF = PT
Aux.Range("k3") = PE
Aux.Range("j3") = "PT"
If PV Then
Aux.Range("k3") = ""
Aux.Range("j3") = "PV"
End If
End With

AbreRealterm0
Pausa
If Aux.Range("i3") = "" Then
'*****************************************
If PT Then
Cad = "PV0"
RT.PutString (Cad)
Pausa
Cad = "PE" & PE
RT.PutString (Cad)
Pausa
Cad = "PT1"
RT.PutString (Cad)
Pausa
End If
'*****************************************
If PV Then
Cad = "PT0"
RT.PutString (Cad)
Pausa
Cad = "PV1"
RT.PutString (Cad)
Pausa
End If
'*****************************************
TF = Now + 5 / 86400
'Prog = "AperturTextoComoExcel"
Prog = "LecturaComoTexto2"
Sheets("Medidas").UsedRange.Rows.Delete
Application.OnTime TF, Prog

End If


End Sub


Private Sub Cancelar_Click()
With Formulario
.Hide

End With

End Sub

Generador Sudokus III.

En entradas anteriores he descrito como hacer un generador de sudokus en excel, sin programar. El único problema es que en ambas entradas generamos sudokus ya resueltos, sin las correspondientes celdas en blanco que caracterizan a todo sudoku.

Esta vez, por último, vamos a colocar aleatoriamente las celdas en blanco que todo sudoku debe tener

Entradas anteriores sobre este tema:
Generador elemental de Sudokus I
Generador de Sudokus II

He preparado un libro excel que coloca, aleatóriamente, n celdas en blanco, sustituyendo el valor correspondiente por un espacio nulo.

Numero las 81 celdas del sudoku. Numero de izquierda a derecha y de arriba abajo:

1	2	3	4	5	6	7	8	9
10	11	12	13	14	15	16	17	18
19	20	21	22	23	24	25	26	27
28	29	30	31	32	33	34	35	36
37	38	39	40	41	42	43	44	45
46	47	48	49	50	51	52	53	54
55	56	57	58	59	60	61	62	63
64	65	66	67	68	69	70	71	72
73	74	75	76	77	78	79	80	81

• Necesito crear un indice con todas las celdas, con todos los números que indica la dirección de cada celda.
• Por otra parte, para poder localizar un determinado valor, necesito que todos los números que me indica el número de celda tenga el mismo número de dígitos. El truco utilizado es empezar a contar en 10 y terminar en 90. De esta manera todas esas direcciones tienen dos dígitos.
• Ademas, añado o concateno un * antes y otro * después. Si no hacemos esto generamos direcciones adicionales. Si concatenamos 11 y 12 y 13, p.e., 111213 y tomamos los números de 2 en 2 vemos que apare un 21. Si concatenamos *11**12**13* evitamos ese error.

En Aux2:

• La concatenación la hice en un libro auxiliar y luego la copié el valor en Aux2!D2.
• En C2 busco un valor aleatorio entre 0 y 80. Con ese valor busco la dirección de la celda en el indice con =EXTRAE(D2;C2*4+1;4).
• En d3, reemplazo el valor encontrado en el paso anterior por un nulo. Voy repitiendo estos dos pasos hasta la última celda.
• En la columna F paso a valor numérico las claves encontradas con =SUSTITUIR(E2;"*";"")+0 (equivale a =VALOR(SUSTITUIR(E2;"*";"")))
• En G2 indico el numero de celdas en blanco del sudoku. Con ese valor genero en H2 un rango, el rango que contiene las direcciones de las n celdas que aparecerán en blanco.
• Necesito conocer las celdas que aparecerán en blanco, pero con sus direcciones ordenadas. 
• Creo el rango con nombre  RanBlan (=INDIRECTO(Aux2!$H$2)). Este rango incluye solamente las n primeras direcciones de la columna F, que son las que quedaran en blanco.
• Para cada dirección, la ordenada, en la columna B, cuento el número de veces que aparece en RanBlan, con =CONTAR.SI(RanBlan;$B2). Solo hay dos valores posibles, cero o uno.
• Para pasar los valores del sudoku generado, el que tiene los 81 valores posibles, necesito pasar los valores que hay en la matriz de 9x9 a una matriz de una columna.
• Para trasponer la matriz cálculo la fila y la columna que se corresponde con la dirección de la celda con =ENTERO(($B2-10)/9)+1 para fila y con =RESIDUO(($B2-10);9)+1 para columna. Recordemos el truco de empezar en 10 la numeración de las celdas.
• Utilizo el rango con nombre, ver administrador de nombres, SudoF =Generador!$B$14:$J$22.
• El valor que debe aparecer en el sudoku final, para cada dirección, es =SI(I2=1;"";INDICE(SudoF;$J2;$K2))
• Ya tengo los valores en una matriz de una columna, queda trasponer esa columna a una matriz de 9*9. En Aux2!o3:w11 paso las direcciones de columna a matriz con =$N3*9+O$2.
• En o14:w22 paso los valores de columna a matriz 9*9 con =INDICE($L$2:$L$82;O3)
• Este método no garantiza que la quinta regla del sudoku se cumpla, puede que al final de todo este proceso la solución no sea única. Cuantos mas espacios en blanco haya mas probable es que no nos salga un sudoku de solución única.

En la portada, hoja Generador:

• Sitúo una barra de desplazamiento con la que se puede modificar el número de celdas en blanco, vinculada con la celda Aux2!$G$2. Limitada entre 20 y 60 blancos. Lógicamente estos límites se pueden cambiar a gusto del usuario.
• El sudoku final queda en el rango m14:u22. Paso los valores de la hoja Aux2 a la principal con =Aux2!O14.

Hasta el momento he encontrado un tipo de combinación de datos que hace que el sudoku pueda presentar mas de un resultado, pero para resolverlo tendría, a simple vista, que hacerlo programando. Creo que es muy complejo como para resolverlo solo con fórmulas. Imaginemos dos columnas distintas, incluidas en la misma terna de columnas. 

• En la primera columna tenemos el valor A, en la segunda columna y en la misma fila tenemos un valor B. En otra fila, en otra región, tenemos en la primera columna un valor B y en la segunda un valor A. 
• Si intercambiamos A por B y B por A:
• Tanto la primera columna como la segunda columna mantienen A y B, sin repetirse ni en la columna ni en las filas afectadas.
• Las dos filas afectadas mantienen A y B sin repetirse sin afectar ni a las columnas ni a las regiones.
• En estos casos no se puede tapar con un espacio los cuatro valores (2 A y 2 B).

Permutaciones aleatorias de 9 elementos en excel. Generador aleatorio de sudokus.

Como complemento a la entrada anterior, generador elemental de sudokus, desarrollo una manera de generar una permutación aleatoria de 9 elementos. Al pulsar F9, tecla de recalculo, nos da un nuevo valor a la permutación.

Parto de la cadena alfanumérica Cad="123456789".

•  La función  =ALEATORIO.ENTRE(1;9) da un valor entre uno y nueve. Para el valor aleatorio de la permutación me vale ese valor. Vease la hoja Aux rango o1:w3 del libro excel.
• Un segundo paso consiste en retirar el primer valor encontrado de la cadena Cad. Esto lo hago con la función =SUSTITUIR(O1;O3;""), que se puede leer sustituye en Cad el valor o3 por una cadena nula.
• Genero un número aleatorio entre 1 y 8, con =ALEATORIO.ENTRE(1;8).
• Con ese valor extraigo el numero que se encuentre en esa posición de la cadena Cad.
• Repito este cálculo hasta llegar a la última posición de la permutación.

Como puede verse en el libro repito este proceso para "barajar" las columnas, las filas y las ternas del sudoku original.

<<Anterior                  Siguiente>>

Generador elemental de sudokus con Excel

Nunca me había planteado este tema, como generar un sudoku, me había limitado a resolver alguno. 

Como en la anterior entrada, en el entorno de como utilizar o resolver algunos eventos de hoja en excel, describo como hacer, con programación, una ayuda para resolver sudokus.

Nunca me lo había planteado, ni siquiera pensado en el tema ¿Como puedo generar uno?

De momento un primer paso, un generador elemental, y muy sencillo, de sudukus.

Partimos de un sudoku ya resuelto, con todos sus números correctamente puestos. La pregunta que me hice es ¿Y si convierto esos números absolutos en indices de un rango?

• Creo un rango de celdas en el que coloco los nueve números del sudoku. Aleatoriamente  (en el libro anexo, de momento a mano) los números se reparten en el rango B1:J1. 
• Utilizo la variable con nombre Num=Generador!$B$1:$J$1
• El sudoku ya resuelto lo pongo en la hoja Aux, rango B2:j10
• Con =INDICE(Num;Aux!B2) utilizo el valor del sudoku ya resuelto como indice.
• Esto nos da un sudoku resuelto, sin celdas en blanco. Solo nos queda copiar los valores en otro lugar y borrar aquellas celdas que deseemos que aparezcan en blanco.

Consideraciones de cara a realizar un generador un poco mas complejo. Las supongo ciertas, de momento sin comprobar.

• El sudoku esta compuesto de tres ternas de tres filas, y de tres ternas de tres columnas. Las tres ternas, tanto de filas de de columnas (cada una por su lado) se pueden reordenar. La primera terna se puede poner como segunda o como tercera, la segunda como primera o como tercera, etc. Lo mismo para las ternas en columna. Al movérlas en bloque, si es fila, no modificamos los números incluidos en cada columna o, si es columna, las filas.
• A su vez, dentro de una terna   fila podemos reordenar sus tres filas. De manera similar podemos actuar con las columnas.
• 
  

Siguiente>>

Eventos en hojas excel con VBasic.

He preparado una herramienta para facilitar, facilitar no resolver, la resolución de sudokus. En una hoja excel pongo a la izquierda el sudoku, con sus valores y sus separaciones, y a la derecha pongo un cuadro similar que da los posibles valores de cada celda si en el original está en blanco o el valor de la celda original si esta no está en blanco. ¿Como lo hago? Al cambiar el valor de una celda lanzo el proceso que encuentra los posibles valores que puede tomar esa celda. Como respuesta a ese evento lanzo un procedimiento. 

Estamos en lo de siempre cuando nos referimos a los eventos. Un evento es cualquier acción reconocible por la aplicación que realicemos sobre, en este caso, una hoja excel. Los eventos son actuaciones como seleccionar o deseleccionar una hoja, hacer doble clic, modificar un valor, recalcular el valor de las fórmulas, etc.

Como respuesta a cualquiera de estas acciones podemos querer un cierto tipo de respuesta, normalmente una respuesta mas compleja que la que se pueda dar con formulas y formatos condicionales. En definitiva una respuesta que necesite programación.

La respuesta a los eventos "de hoja" tiene un nombre concreto para cada evento y debe estar situada en módulo de la propia hoja. Pinchando con el botón derecho del ratón en la solapa de la hoja podemos entrar en el módulo con "ver código". Como ya he dicho hay una serie de procedimientos con su propio nombre, y con sus propios parámetros, aunque el nombre de las variables se puede cambiar, que responden al evento correspondiente.

Cuando la hoja activa pierde el foco. Al cambiar de hoja.

******************************************************

Private Sub Worksheet_deactivate()

'MsgBox "Adios"

End Sub

******************************************************

Al seleccionar la hoja.

******************************************************

Private Sub Worksheet_Activate()

'MsgBox "Hola"

End Sub

******************************************************

Al hacer doble clic

******************************************************

Private Sub Worksheet_BeforeDoubleClick(ByVal Ran As Range, Cancel As Boolean)

'MsgBox "Clic-clic"

End Sub

******************************************************

Al recalcular una hoja.

*****************************************************

Private Sub Worksheet_Calculate()

'    Columns("A:F").AutoFit

End Sub

******************************************************

******************************************************

Al utilizar el botón derecho del ratón.

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _

        Cancel As Boolean)

        

       ' MsgBox "no lo hagas"

        End Sub

Al hacer doble clic: Al hacer doble clic nos lleva de la celda con posibles contenidos a la correspondiente celda en el sudoku.
******************************************************

Private Sub Worksheet_BeforeDoubleClick(ByVal Celda As Range, Cancel As Boolean)

F = Celda.Row
C = Celda.Column
If C >= 13 And C <= 21 And F >= 2 And F <= 10 Then Celda.Offset(0, -11).Select
End Sub

******************************************************
*****************************************************  Private Sub Worksheet_SelectionChange(ByVal Celda As Range)

'MsgBox Celda.Address


End Sub

******************************************************

En mi facilitador de sudokus manejo el evento "Al cambiar el valor de una celda:"

******************************************************

Private Sub Worksheet_Change(ByVal Celda As Range)

Dim D, F, C

' la variable pasada,Celda, es un rango, se refiere a la celda que acabamos de cambiar el valor.

' No todas las celdas de la hoja forman parte del sudoku. Lo primero que tengo que hacer es conocer si la celda modificada está o no esta en el rango del sudoku, es una de celdas que desencadenan el procedimiento "Al cambiar". Para ello debo conocer la fila y la columna, dentro de la hoja, que ocupa la celda.

' 

F = Celda.Row ' Fila de la celda.

C = Celda.Column ' Columna de la celda.

D = Celda.Address ' Dirección de la celda. Aunque luego no la utilice

' El rango del sudoku es B2:J10. Por tanto, las celdas útiles están situadas entre la columna 2, fila 2 y la columna 10, fila 10

If C >= 2 And C <= 10 And F >= 2 And F <= 10 Then

RangoCompuesto ' Proceso datos.

End If

End Sub

******************************************************

Cuando se incluye o modifica un número en una celda:

• El proceso recorre todo el rango del sudoku, pasando por todas las celdas.
• Las reglas del sudoku dicen que un número no debe repetirse ni en la línea, ni en la columna, ni en el rango de 3*3 que contiene a la celda en cuestión. 
• Debo, antes de ver si un número se repite o no, encontrar el rango compuesto de la celda en cuestión.

Sub RangoCompuesto()

Dim D, V, F As Integer, C, Ran, R1, R2, Cad, Num, Val

Cad = "123456789"

With ActiveSheet

For Each Celda In .Range("b2:j10") ' Rango del Sudoku

Val = Trim(Celda.Value) 'Valor de la celda

If Val = "" Then

'***********************************************

'Encontrar fila y columna de la celda procesada se puede hacer de dos maneras, a partir de la dirección o directamente con row y column. En este caso, puede que resulte mas sencillo utilizar la dirección.
D = Celda.Address 'Dirección de la celda.

V = Split(D, "$")  'Convierte o pasa la dirección a una matriz columna (en letra) y fila

'F = Celda.Row

'C = Celda.Column - 2

F = V(2)

C = Asc(V(1)) - 66 ' En este caso la primera columna del sudoku es la B, ascii 66. En este caso interesa que la primera columna, en número, sea cero.

Ran = "b" & F & ":" & "J" & F & "," & V(1) & 2 & ":" & V(1) & 10

R1 = Int((F - 2) / 3) * 3 ' Esta operación permite encontrar la celda de mas arriba y mas a la izquierda de cada grupo 3*3 de celdas del sudoku.

R2 = Int(C / 3) * 3

D = .Range(.Cells(R1 + 2, R2 + 2), .Cells(R1 + 4, R2 + 4)).Address

Ran = Ran & "," & D

.Range(Ran).Select

' Encontrado el rango, recorre todas las celdas de ese rango con un "para cada celda en un rango"

'******************************************************

For Each Celda2 In .Range(Ran)

Num = Celda2.Value

Cad = Replace(Cad, Num, "") ' La cadena Cad contiene los nueve números utilizados en el sudoku. El proceso Reemplaza todo numero encontrado con una cadena nula ""

Next

'******************************************************

End If

If Val = "" Then

Celda.Offset(0, 11) = "*" & Cad & "*" ''Escribe lo que queda de la cadena Cad

Else
''Si la celda contiene un número, en la imagen escribe ese número.

Celda.Offset(0, 11) = Val 

End If

Cad = "123456789"

Next

'  .Columns("m:u").AutoFit

End With

'

End Sub

Manera de calcular los cortes de unas patas en aspa con Excel.

Esta vez el supuesto es construir una mesa con las patas en aspa o en X. Con el libro excel calculo longitudes y cruces de las patas. Para el cálculo de unas patas en aspa, en principio, considero dos valores, la altura que queremos tener y la separación entre los extremos de las patas. Este supuesto es para patas simétricas. Las patas se cruzan en el centro del aspa. La parte carpintera permite, una vez fijadas la separación entre los extremos de las patas y ancho del listón, calcular el ángulo de corte y las distancias que indican por donde se cruzan ambas patas. Con la barra de desplazamiento de la hoja "Inicio" se selecciona el ángulo de corte (en décimas de grado) hasta alcanzar una la altura deseada.

Si queremos construir físicamente el aspa:

• Desde uno de los extremos trasportamos el ángulo con un transportador de ángulos, o llevamos la distancia aa' desde la esquina contraría siguiendo el listón.
• Llevamos las distintas medidas, según la gráfica de la hoja "Inicio"

Mi propia función en Excel:

• Los cálculos teóricos son básicamente trigonométricos.
• No son especialmente complejos, pero como llevo muchísimos años sin trabajar con senos, cosenos, tangentes, etc, me ha costado mas de la cuenta.
• Además, como he llegado a una función un poco demasiado compleja para mi perdida base matemática, decidí no intentar resolverla. Para mi era demasiado compleja y además quería resolverla de otra manera, con excel, tanteando con distintos valores hasta encontrar el valor mas aproximado posible.
• De paso toco de nuevo la posibilidad de hacer nuestras propias funciones con vbasic para excel.
• La función no resuelta es Altura=(DistEntrePatas-AnchoListón/SENO(RADIANES(a)))*TAN(RADIANES(a))

Este método solo funciona para funciones, o tramos de funciones,  continuas y crecientes o decrecientes pero que no presenten ni picos ni valles. Las sucesivas aproximaciones se hacen fijando dos extremos, dos valores que sabemos que definen un tramo que cumple lo antes dicho, continuidad, crecimiento (o decrecimiento) y ausencia de picos y valles.

• Hay que calcular el ángulo que nos va a dar la altura deseada. Fijamos, por tanto, la la altura deseada, en este caso en la hoja Aprox F2, el ancho de tabla y la separación entre patas.
• Calculamos el valor medio de ambos extremos, en este caso el ángulo en grados.
•  Calculamos, en este caso, las alturas en función de los grados, de los tres valores, límite inferior, límite superior y valor medio.
• Si la altura correspondiente al valor medio supera la altura deseada, el valor del límite superior pasa a ser el valor medio.
• Si la altura correspondiente al valor medio es inferior a la altura deseada, el límite inferior pasa a ser el valor medio.
• Si la función fuese decreciente el cambio de límites sería al contrario.
• Cada vez que se repite este proceso se aproximan los límites inferior y superior, hasta alcanzar el valor que resuelve nuestra ecuación.
• En el ejemplo hago 64 repeticiones, mas que suficientes para resolver mi irresoluta ecuación.
• Por otra parte, es poco práctico llenar una hoja de fórmulas bastante complejas cuando podemos crear una función en vbasic que nos resuelve nuestro problema.
• Con alt+f11 podemos ver los módulos con la programación vbasic del libro y ver las distintas funciones creadas para resolver la función que resuelve nuestro cálculo.
• En la hoja Aprox, celda L3 y columna E, utilizo un par de  funciones creadas por mi. Su funcionamiento es idéntico a cualquier otra función propia de excel.

Valores de un control que dependen de otro anterior.

Tengo dos controles, dos listas desplegables. El tema es que tengo hasta nueve colores, situados en tres bandas pintadas sobre una resistencia (electrónica). La primera banda me indica el primer dígito del valor, en ohmios, de una resistencia. La segunda banda me indica el segundo dígito de ese valor y la tercera multiplica los dos dígitos anteriores por 10, 100, 100,....

Libro excel

Color		1ªFranja	2ªFranja
Marron	10	Marron	Negro
Rojo	12	Marron	Rojo
Naranja	15	Marron	Verde
Amarilla	18	Marron	Gris
Verde	22	Rojo	Rojo
Azul	27	Rojo	Violeta
Gris	33	Naranja	Naranja
	39	Naranja	Blanca
	47	Amarilla	Violeta
	51	Verde	Marron
	56	Verde	Azul
	68	Azul	Gris
	82	Gris	Rojo

Con dos desplegables puedo seleccionar el primer color con el primer desplegable y el segundo color con el segundo desplegable. 

El único problema es que no todas las combinaciones posibles se fabrican comercialmente. Solo se pueden dar unas pocas combinaciones, según la tabla anterior. Algunos colores de la primera banda pueden combinar con catro colres, otros con dos, etc...

¿Puedo variar los item del segundo desplegable en función del color del primero?

¿Como vario los item del segundo desplegable en función del color del primero?

Por supuesto, se puede, pero hay que utilizar rangos con nombre.

• El primer desplegable no tiene problema, se trata como cualquier otro, no tiene nada en especial. Por tanto indicamos el rango de entrada (Aux!$J$15:$J$21) y la celda a la que está vinculado (Aux!$C$14)
• Antes de empezar a trabajar con el segundo desplegable hay que pensar como vamos a conocer el rango de los segundos colores. Podría hacerlo a mano, en el ejemplo hay pocas líneas y, además, no varían con el tiempo, pero si hubiese una gran cantidad de líneas sería francamente complicado.
• En este caso, se puede hacer de varias maneras, utilizo primero la función INDIRECTO para recuperar el nombre del color, en la celda vinculada al desplegable esta la posición del color dentro de la lista de colores. =INDIRECTO("j" & Aux!$C$14+14)
• Cuento el número de veces que aparece el color en la segunda lista, la lista de los dos colores con =CONTAR.SI($L$15:$L$27;$A$16)
• Hasta el momento podemos utilizar nombres o rangos tal cual los escribimos habitualmente.
• Encontramos la primera aparición, en la segunda lista, del color deseado. =COINCIDIR($A$16;$L$15:$L$27;0)+14
• Generamos, como texto, el rango en el que se encuentran los segundos colores. =("Aux!$m$" &C16 & ":$m$" & C16+B16-1)
• Entramos en administrador de nombres y añadimos un nombre, en este caso SColor y la función indirecto, con la dirección generada en el paso anterior.  =INDIRECTO(Aux!$D$16)
• Aparece otro pequeño problema, cada color combina con un número de colores distinto. Si la celda vinculada al segundo desplegable tiene un número superior al de colores del desplegable nos provoca un error.
• La solución encontrada es vincular el segundo desplegable a una celda distinta por color del primer desplegable.
• Con el administrador de nombres creamos una variable (CV).=INDIRECTO("Aux!h" &Aux!$C$14+14)
• El nombre del segundo color se obtiene con la función  =INDICE(SColor;CV)

Código de colores de una resistencia con excel. Directo e inverso

Para conocer el valor de una resistencia se utiliza un código de colores a cuatro bandas, la primera banda se reconoce porque es la más cercana al borde del cuerpo de la resistencia mientras que la cuarta banda (la tolerancia) está más separada respecto a las otras tres. 

• Los colores posibles de las dos primeras banda son 9. Cada uno corresponde a un número entre 0 (negro) y 9 (blanco) , siguiendo el orden de los colores del arco iris (negro, marrón, rojo, naranja, amarillo, verde, azul, gris y blanco).
• La primera banda nos indica el primer dígito del valor de resistencia. La segunda banda nos da el segundo dígito de dicho valor. Los dos dígitos de las primeras dos bandas nos dan un número que puede variar entre 0 y 99.
• La tercera banda es el multiplicador, es decir, un factor con el cual debemos multiplicar el número de las dos primeras bandas. Por ejemplo, si el valor de las primeras bandas es 47 y el multiplicador es 1000 (o 1K) el valor de resistencia será de 47.000 ohms (47K). En la tabla pueden ver todos los colores, las bandas y los valores correspondientes. En la parte alta del diseño podemos ver un ejemplo concreto.

Para resistencias codificadas con 4 bandas el más conocido se llama E12 y está compuesto, como su nombre lo indica, por una serie de 12 números que son: 10, 12, 15, 18, 22, 27, 33, 39, 47, 56, 68, 82 y que se repiten para cada década del multiplicador. En la figura podemos ver todos los valores estándar E12 posibles que son 108 (12 números x 9 multiplicadores posibles). Por ejemplo, una resistencia con las dos primeras bandas rojas tendrá un valor numérico de 22 pero en base a la tercera banda el valor final podrá ser de 0,22 ohms, 2,2 ohms, 22 ohms, 220 ohms, 2,2K, 22K, 220K, 2,2M o 22M.

Cuando empecé con la calculadora de resistencias en Excel ya sabía que no todas las posibles combinaciones de colores se correspondían con un valor comercial, pero consideré que, en un primer intento no iba a contar con ello, por lo que en el libro excel se pueden seleccionar combinaciones de colores que que no se fabrican. A la inversa, a partir de un cálculo matemático en el que encontramos un valor de resistencia, la hoja de cálculo solo da valores comerciales.

La hoja de cálculo permite saber el valor de una determinada resistencia a partir de sus colores (3 colores) y el conocer los colores que corresponden a un determinado valor de una resistencia teórica como suma de hasta tres valores comerciales, en ohmios, siempre y cuando el valor teórico sea entero y superior a 10 ohmios. Creo que haré una nueva versión contemplando esos detalles.

Funciones utilizadas:

• Indirecto(Cad):Convierte una cadena alfanumérica en una dirección excel.
• Indice(). Devuelve el contenido de una celda incluida en un rango.
• Extrae(texto;pos. inicial;N. Caract.). Devuelve una serie de caracteres de una cadena.
• Coincidir(valor;matriz;tipo busq.). Encuentra un valor en un rango de valores.
• Si.Error(valor;valor si error). Si se produce un error devuelve un valor determinado por el segundo parámetro.

Variables y rangos con nombre:

• CFr1:=Aux!$A$3:$A$11 Colores de la primera franja.
• CFr2:=Aux!$A$2:$A$11. Colores de la segunda franja.
• CFr3:=Aux!$C$2:$C$10. Colores de la tercera franja.
• VCom1:=Resistencia!$D$13. Valor comercial 1.
• VCom1:=Resistencia!$F$13. Valor comercial 2.
• VComer:=Aux!$K$2:$K$92. Valores comerciales.
• VForm:=Resistencia!$B$13. Valor teórico de la resistencia.

Formato condicional en las celdas B2 a B5 de la hoja Resistencia.

Valor de una resistencia según sus colores:

• El color de cada una de las bandas se selecciona mediante un desplegable. El color, en este caso, aparece como un literal. 
• Cada desplegable tiene una celda vinculada con el valor seleccionado. En este caso de C2 a C5.
• Los valores numéricos correspondientes a cada color de cada banda están en la hoja Aux.
• Para recuperar cada valor utilizo, en este caso, la función INDIRECTO. Con una sola fórmula calculo el valor total (=(INDIRECTO("Aux!B" & C2+2)*10+INDIRECTO("Aux!B" & C3+1))*INDIRECTO("Aux!d" & C4+1))
• Este cálculo también se puede hacer mediante la función INDICE. (=(INDICE(Aux!B3:B11;$C2)*10+INDICE(Aux!B2:B11;$C3))*INDICE(Aux!D2:D9;$C4). Los rangos a los que se refieren las funciones INDICE pueden sustituirse por un nombre de rango, que previamente se haya dado de alta con el administrador de nombres.
• Por último, hay  una pequeña comprobación de que un determinado valor esta en la tabla de valores comerciales. (Celdas H y J).

Formato condicional:

• Los formatos condicionales de las celdas con formato condicional, en este caso, dependen del valor de la celda asociada al desplegable. Así que  el formato condicional depende de una fórmula. Utilizo la formula =c2=1 para dar de fondo un color marrón. Es un poco pesado, hay que hacerlo de uno en uno.

Valores comerciales que nos aproximan a un valor calculado:

• Aquí utilizo la función COINCIDIR(VBuscado;Rango en donde se busca; tipo de búsqueda), en este caso tipo de búsqueda=1, con el que encuentra, en una lista ordenada, el último número que es menor o igual al valor buscado. Esta función nos devuelve la posición en la que está el valor buscado, no el valor encontrado. Con la función INDICE recupero el valor encontrado.  =INDICE(VComer;COINCIDIR(VForm;VComer;1)), utilizando además nombres en vez de rangos.
• Las otras dos resistencias, en serie, se calculan restando al valor inicial las resistencias encontradas y, con ese valor, repetir el proceso de búsqueda.

Segundo color dependiendo del primero

PSeudo Osciloscopio con Excel.

Se trata de utilizar un gráfico del tipo XY para ver, y medir, la forma de una señal eléctrica.

Tengo un par de proyectos pendientes, uno de ellos poner en marcha de nuevo una vieja mobylette campera. La bobina es nueva, los platinos son nuevos, el circuito eléctrico está bien pero no da chispa, no es que de chispa fuera de tiempo, es que no da chispa.

Con un microprocesador Arduino Uno construí un pequeño aparato de medida para medir la señal en los platinos. Básicamente la cosa consiste en medir, mediante el conversor A/D del arduino, el nivel de tensión y escribir en una tarjeta SD ese valor y el tiempo, en microsegundos, en el que se hace la medida. Anoto el valor de conversión A/D leido no el valor en voltios.

El conversor A/D de Arduino es lento, para medir señales de alta frecuencia no vale, pero para este aparato de medida, si.

Otro tema es ver la señal. Hice, utilizando una pantalla tft, un preliminar para ver la señal en el propio aparato. Ya en una ocasión anterior había tocado este tema, ver la señal mediante un gráfico excel, así que decidí terminarlo.

• Los datos los paso a excel, de momento, mediante un copia-pega. Habro el fichero con las medidas y las copio a Excel (Datos0)
• El gráfico, como ya he dicho, es del tipo XY con rango variable. Para variar el rango utilizo la función desref().
• El concepto es "voy a ver n puntos a partir de un punto punto determinado.
• Tanto para seleccionar el primer punto como para determinar el número de puntos a ver utilizo barras de desplazamiento.

Variables y rangos con nombre:

• BDI (=Aux!$A$2) Celda vinculada con la barra de desplazamiento que selecciona el primer punto de la gráfica.
• BDI (=Aux!$B$2) Celda vinculada con la barra de desplazamiento que selecciona el número de puntos de la gráfica.
• Mcrs. (=DESREF(Datos!$D$2;BDI;0;BDN;1)) Rango del eje de tiempos.
• Vol, =DESREF(Datos!$C$2;BDI;0;BDN;1) Rango de los valores de tensión en el eje Y.
• Sonda, =Aux!$A$5. Como la tensión de salida en los platinos es superior a 5 v. tuve que construir un divisor de tensión para poder medir tensiones superiores a 5 v. Es el factor de división.
•  PValorX, =INDIRECTO("datos!$a$" & Aux!$A$2+2). Es el valor del primer punto a representar. Este valor se resta a todos los valores vistos con el fin de que el primer valor en el eje de tiempos de la gráfica sea cero.
• Leyenda, sin uso como variable con nombre, ="V.Min "&TEXTO(Aux!A3;"0,000")&CARACTER(10)&"V.Max="&TEXTO(Aux!A2;"0,000")&CARACTER(10)&"Microseg="&MAX(Mcrs) &CARACTER(10)&"Sonda="&Sonda

Otras funciones utilizadas:

• TEXTO(F2;"0,000") : Convierte el valor de F2 en texto con el formato deseado.
• Max.
• Min.

Gráfico:

La primera barra de desplazamiento selecciona el primer punto. La segunda, inmediatamente debajo, el número de puntos. Hay una tercera barra sin uso. La serie de la gráfica utiliza rangos con nombre, según:

 =SERIES(Aux!$H$2;SeudoOsciloscopio.xls!Mcrs;SeudoOsciloscopio.xls!Vol;1)

Datos0:

Columna A: Tiempos, sin acumular, entre medidas.

Columna B: Valores A/D medidos.

Columna C: Tiempos acumulados.

Columna D: Igual a columna B.

Datos:

Columna A: Tiempos acumulados. Igual a Datos0!C

Columna B: Valores A/D. Igual a Datos0!B

Columna C: Valor A/D convertido a voltios, teniendo en cuenta el factor del divisor de tensión (sonda).

Columna D: Tiempos, descontado el primer valor del eje de tiempos.

Aforador.

• Me llega una pregunta a través de mi blog AhoraMeDedicoAlCacharreo, alguien esta intentando construir un aforador con un Arduino y me pide ayuda con las formulas. 
• El aforador mide el nivel de ocupación de un depósito de agua cilíndrico tumbado. De alguna manera se mide la altura o nivel de agua con respecto al suelo.
• Para emular el sensor de nivel utilizo un potenciometro o divisor de tensión en A0.
• El espacio vacío, visto desde el alzado, deja un segmento circular sin ocupar. El área del segmento circular es el área del sector circular (quesito) menos el área del triangulo definido por los radios y la cuerda del segmento  vacío (ver gráfica).

Me entra la curiosidad y me pongo a ello. Recupero algunos conceptos ya casi olvidados.

• Sector circular. "Quesito" que contiene el segmento circular.
• Segmento circular. Intento encontrar las fórmulas en internet , no me entero de nada, y decido desarrollarlas por mi mismo. A veces es necesario este tipo de decisiones para refrescar conocimientos.
• Coseno del ángulo suplementario: 
• Teorema de Pitágoras.

Empiezo el desarrollo de las fórmulas que me permitan calcular los volúmenes correspondientes. Creo que no me he equivocado, creo que las fórmulas son correctas, pero pudiera suceder que no. Ahí va mi borrador.

• No me entiendo a mi mismo. No se por que calculo de una manera tan rara el tercer lado del triángulo (1/2 de b), si con aplicar directamente  Pitágoras  vale. Conozca la hipotenusa y uno de los catetos, luego R²=h²+b²=>b²=R²-h²=>b=raiz(R²-h²). Podría justificarme diciendo que así utilizamos, y refrescamos, trigonometría.
• Preparo una batería de medidas para comprobar un posible error al calcular las fórmulas.
• H, altura del nivel del líquido, H=2*R (2*Radio). Esto equivale a barril lleno. Esta prueba la cumplen.
• H, altura del nivel del líquido, H=R (Radio). Esto equivale a barril medio lleno. Esta prueba la cumplen.
• Dos medidas con H=2R-x y H=x. El espacio libre en la primera medida debe ser igual al espacio lleno de la segunda medida y viceversa. Esta prueba también la cumplen para cualquier probado de x.

• Si menos de la mitad del barril está lleno el segmento  circular menor es el espacio ocupado, no el espacio vacío.

• Cuando barril tiene un nivel menor a la mitad la altura del  barril, las formulas calculadas siguen valiendo. La altura del triangulo y el coseno del ángulo pasan a ser negativos. Para cosenos negativos la función ACOS de excel y la función  ATAN2 de arduino devuelven el ángulo suplementario, aquel que sumado al ángulo da 180º, lo que al repartir el área del circulo proporcionalmente nos da el área ocupada por el resto del circulo. Como el área del triángulo da negativo, recordemos que h es negativo, al restarla, añadimos al resto del circulo ese valor,  sumando todo ello la parte no ocupada del barril.

Llevo las fórmulas a excel. Utilizo variables con nombre.

• A=H-R
• Area_Sector=R^2*ACOS(A/R)
• Area_Segmento=Area_Sector-Area_Triangulo
• Area_Triangulo=A*RAIZ(R^2-A^2)
• H=$Aforador.$E$2
• L=$Aforador.$B$2
• R=$Aforador.$A$2
• Radio=$Aforador.$A$2
• V=$Aforador.$D$2
• Volumen_ocupado=V-Volumen_Segmento
• Volumen_Segmento=Area_Segmento*L
• Traduzco el desarrollo en excel a código Arduino.

Código Arduino:

#include <math.h>



double Pi=3.1415926536;

double Radio=50,Longitud=100;

double V=pow(Radio,2)*Pi*Longitud, h,AT,HAnt,ASec,ASeg,VSeg,VOcu,PorCen;



unsigned long H;



void setup()

{

Serial.begin(9600);

Serial.println(V);

Calculos();

}



void loop()

{

    H=analogRead(A0)*2*Radio/1023;

  if(H!=HAnt)

  {Calculos();

  //  Serial.println(H);

    HAnt=H;

  }

 

}



void Calculos()

{



  h=H-Radio;

  // Area del triangulo h*RAIZ(R^2-h^2)

  AT=h*sqrt(pow(Radio,2)-h*h);

 // area del sector R^2*ACOS(A/R)

 ASec=pow(Radio,2)*MiACos(h/Radio);

 //Area_Sector-Area_Triangulo

 ASeg=ASec-AT;

 //Volumen segmento Area_Segmento*Longitud

 VSeg=ASeg*Longitud;

 VOcu=V-VSeg;

 PorCen=VOcu*100/V;

 Serial.println("****************************************");

 Serial.println(Radio);

 Serial.println(Longitud);

 Serial.println(V);

 Serial.println(AT);

 Serial.println(ASec);

 Serial.println(ASeg);

 Serial.println(VSeg);

 Serial.println(VOcu,2);

 Serial.println(PorCen,2);

 Serial.println("****************************************");

}



unsigned long Fact(long N)

{unsigned long I;

 unsigned long R=1;

 for(I=1;I<=N;I=I+1)

 {

  R=R*I;



 }

return R;

}



float Grados(float Rd)

{

  float G=(Rd*360)/(2*Pi);

return G;



}



float Radianes(float G)

{

  float Rd=(G*Pi)/180;



return Rd;



}











float Pi4()



{

float signo=1,i;

float p=0,x4=1/2+1/3,x1,x2,x3 ;//1/2+1/3:



for(i=1;i<40;i=i+2)

{

  x1=signo/i;



  x2=1/pow(2.0,i);

  x3=1/pow(3.0,i);



/*

if(i>15)

 {

  x2=1/pow(2.0,15);

  x2=x2/pow(2.0,i-15);

  x3=1/pow(3.0,15);

  x3=x3/pow(3.0,i-15);



 }*/

//p=p+signo/(i*pow(2.0,i))+ signo/(i*pow(3.0,i));

p=p+x1*x2+x1*x3;

signo=signo*(-1) ;

/*Serial.print(i,0);

Serial.print(" ");

Serial.print(x2,20);

Serial.print(" ");

Serial.println(x3,20);*/

}



return p;

}







float MiACos(float R)

{

float S, C, T;

C = R;

S = sqrt(1 - pow(R , 2));









return atan2(S,C);



}





float MiASen(float R)

{

float S, C;

S = R;

C = sqrt(1 - pow(R , 2));







return atan2(S,C);



}