Este curso forma parte de Programa especializado: Excel/VBA for Creative Problem Solving

4.9
estrellas
1,211 calificaciones

95 %

Charlie Nuttelman

34.560 ya inscrito

ofrecido por

Programa especializado: Excel/VBA for Creative Problem SolvingUniversidad de Colorado en Boulder

Acerca de este Curso

30.546 vistas recientes

"Excel/VBA for Creative Problem Solving, Part 2" builds off of knowledge and skills obtained in "Excel/VBA for Creative Problem Solving, Part 1" and is aimed at learners who are seeking to augment, expand, optimize, and increase the efficiency of their Excel spreadsheet skills by tapping into the powerful programming, automation, and customization capabilities available with Visual Basic for Applications (VBA).
In Part 2 of the course, learners will: 1) learn how to work with arrays and import/export arrays from/to Excel using VBA code; 2) learn how to work with text strings and write data to .txt files and import information from .txt files; 3) automate the import, modification, and consolidation of information from multiple worksheets into a central worksheet as well as the import of information from multiple workbooks to a central workbook; and 4) gain experience with creating professional user forms to interface with the user, perform advanced calculations, and manipulate data on the spreadsheet.

Learners who have a foundational understanding of VBA code and programming structures can jump right into Part 2 of the course without taking Part 1 and use the screencasts in Part 1 as reference.

Each module will introduce foundational and broad problems inspired by situations that you might encounter in the real world.  To pass each module, you'll need to pass a mastery quiz and complete a problem solving assignment.  This course is unique in that the weekly assignments are completed in-application (i.e., on your own computer in Excel), providing you with valuable hands-on training.

Fechas límite flexibles

Restablece las fechas límite en función de tus horarios.

Certificado para compartir

Obtén un certificado al finalizar

100 % en línea

Comienza de inmediato y aprende a tu propio ritmo.

Curso 2 de 3 en

Programa especializado: Excel/VBA for Creative Problem Solving

Nivel intermedio

Aprox. 21 horas para completar

Inglés (English)

Subtítulos: Árabe (Arabic), Francés (French), Portugués (europeo), Italiano, Vietnamita, Alemán (German), Ruso (Russian), Inglés (English), Español (Spanish)

¿Podría tu empresa beneficiarse de la capacitación de los empleados en las habilidades más demandadas?

Prueba Coursera para negocios

Fechas límite flexibles

Restablece las fechas límite en función de tus horarios.

Certificado para compartir

Obtén un certificado al finalizar

100 % en línea

Comienza de inmediato y aprende a tu propio ritmo.

Curso 2 de 3 en

Programa especializado: Excel/VBA for Creative Problem Solving

Nivel intermedio

Aprox. 21 horas para completar

Inglés (English)

Subtítulos: Árabe (Arabic), Francés (French), Portugués (europeo), Italiano, Vietnamita, Alemán (German), Ruso (Russian), Inglés (English), Español (Spanish)

¿Podría tu empresa beneficiarse de la capacitación de los empleados en las habilidades más demandadas?

Prueba Coursera para negocios

Instructor

Calificación del instructor

4,89/5 (245 calificaciones)

Charlie Nuttelman

Senior Instructor

Chemical and Biological Engineering

323.875 alumnos

6 cursos

ofrecido por

Universidad de Colorado en Boulder

CU-Boulder is a dynamic community of scholars and learners on one of the most spectacular college campuses in the country. As one of 34 U.S. public institutions in the prestigious Association of American Universities (AAU), we have a proud tradition of academic excellence, with five Nobel laureates and more than 50 members of prestigious academic academies.

Mira cómo los empleados de las mejores empresas dominan las habilidades más demandadas

Obtén más información sobre Coursera para Empresas

Programa - Qué aprenderás en este curso

Calificación del contenido95%(2,514 calificaciones)

Semana

Semana 1

6 horas para completar

Arrays and Array Functions

In Week 1, you will learn all about arrays and array functions. You will learn how to iterate through arrays using nested For...Next loops and For Each...Next statements; you will learn how to import data to VBA from Excel and export data to Excel from VBA; you will learn how to create user-defined array functions; and you will learn about the ReDim Preserve statement for resizable arrays. Week 1 is concluded with a quiz, which unlocks Assignment 1.

6 horas para completar

14 videos (Total 93 minutos), 8 lecturas, 2 cuestionarios

14 videos

What you will learn in this course7m

How the course works4m

How To Switch Sessions of the Course1m

Introduction to arrays9m

Local arrays in VBA5m

Importing/Exporting arrays from/to Excel8m

Using arrays in subroutines and functions8m

User-defined array functions6m

Example 1: SortVector array function and ksmallest8m

Example 2: Extracting diagonal elements from a square matrix4m

Example 3: Residuals of simple linear regression8m

ReDim Preserve8m

Example: ReDim Preserve8m

Assignment 1 preview and instructions1m

8 lecturas

For Mac users1m

The importance of a Course Certificate and the future of higher education5m

If your computer won't open a macro-enabled workbook10m

(OPTIONAL) Will Office Scripts replace VBA?10m

Remember to use your DEBUGGING skills!10m

Quiz 1 solutions and explanations10m

Assignment 12h

Week 2 STARTER file (1 file)

2 ejercicios de práctica

Week 1 Quiz20m

Assignment 1 submission30m

Semana

Semana 2

5 horas para completar

Working with strings and .txt files

Week 2 deals with text strings and text (.txt) files. You will learn about the string functions in Excel and VBA. You will gain practice with string functions as you learn how to create functions in VBA that utilize string functions. You will learn how to extract strings, such as email addresses, from a spreadsheet of mixed string formats. You will also learn how to write information from Excel to .txt files and also to import information in .txt files to Excel. The module is concluded with a quiz and Assignment 2.

5 horas para completar

12 videos (Total 84 minutos), 3 lecturas, 2 cuestionarios

12 videos

How to use string functions in Excel8m

Example: How to create email addresses from Last Name, First Name format7m

How to use string functions in VBA8m

Example: Using string functions in VBA4m

Example: Extracting email addresses from mixed string formats8m

Example: VBA array function for separating strings into component parts8m

Exporting data from Excel to .txt files6m

Importing data from .txt files8m

Importing data from tab-delimited .txt files2m

Example: Morse coder9m

Example: Morse decoder8m

Assignment 2 preview and instructions1m

3 lecturas

Quiz 2 solutions and explanations10m

Assignment 22h

Week 3 STARTER files (11 files)

2 ejercicios de práctica

Week 2 Quiz20m

Assignment 2 submission30m

Semana

Semana 3

5 horas para completar

Iterating through worksheets and workbooks

Week 3 is all about iterating through (automating) worksheets and workbooks. You will learn how to consolidate information from multiple worksheets into a single worksheet and you will learn how to automatically consolidate information found within multiple workbooks into a single worksheet of a single workbook. The automation principles you learn in this module are priceless. Week 3 is concluded with a quiz and a very challenging assignment.

5 horas para completar

12 videos (Total 80 minutos), 2 lecturas, 2 cuestionarios

12 videos

All about worksheets8m

Iterating through worksheets7m

Consolidating information in multiple worksheets into a single worksheet4m

Example: Counting total number of 7's in all worksheets of a workbook5m

Putting it all together: Consolidating employee schedules in multiple worksheets9m

All about workbooks9m

Opening workbooks8m

Example: Importing and consolidating data from multiple files6m

Example: Counting 7's in multiple workbooks5m

Putting it all together: Consolidating employee schedules8m

Assignment 3 preview and instructions2m

How to select a range using the input box method1m

2 lecturas

Assignment 32h 30m

Week 4 STARTER files (5 files)

2 ejercicios de práctica

Week 3 Quiz20m

Assignment 3 submission30m

Semana

Semana 4

5 horas para completar

User forms and advanced user input/output

In Week 4, you will learn about advanced input and message boxes and event handlers. You will learn how to interface with the user in a professional manner using user forms that validate input and prevent the "debug" window and VBE from appearing. You will also learn how to place combo boxes on user forms and populate those combo boxes with information on the spreadsheet. Finally, you will learn how to put it all together and implement a solving technique (bisection method) to solve a real world problem in a user form. Week 4 is concluded with a quiz, which unlocks Assignment 4.

5 horas para completar

14 videos (Total 88 minutos), 1 lectura, 2 cuestionarios

14 videos

Advanced input boxes7m

Advanced message boxes6m

Event handlers4m

Introduction to user forms3m

Creating your first user form10m

Example: Fuel efficiency user form7m

Example: Tank volume user form6m

Dim'ming (or not Dim'ming) variables in user forms1m

Input validation in user forms6m

Introduction to combo boxes, Part 15m

Introduction to combo boxes, Part 26m

Example: Periodic table user form8m

(OPTIONAL) Putting it all together: Conversion Solver user form10m

Assignment 4 preview and instructions2m

1 lectura

Assignment 42h 30m

2 ejercicios de práctica

Week 4 Quiz20m

Assignment 4 submission30m

Reseñas

4.9

314 reseña

5 stars
89,62 %
4 stars
8,49 %
3 stars
1,06 %
2 stars
0,32 %
1 star
0,49 %

Principales reseñas sobre EXCEL/VBA FOR CREATIVE PROBLEM SOLVING, PART 2

por JU27 de jun. de 2020

Very interesting journey through advanced capabilities of VBA for Microsoft Excel. Looking forward for the interesting projects to work on Part 3!

por PN26 de dic. de 2020

very interesting course, very nicely and deeply explained all topics in course. All assignments were quite challenging and very useful for real world applications. looking forward to part 3 of course!

por SJ2 de jun. de 2019

The course provides one the opportunity to work with a lot of concepts/objects in Vba.Be it arrays,working with diffreent workbooks/worksheets/user forms,you have it all in the course.

por CJ21 de oct. de 2020

This was an interesting course and taught me how to use userforms and make much more sophisticated excel/vba files.

I would have liked to learn more about how VBA interacts with charts though.

Ver todas las reseñas

Acerca de Programa especializado: Excel/VBA for Creative Problem Solving

This Specialization is for learners wishing to dramatically change the way that they use Excel spreadsheets by unleashing the power to automate and optimize spreadsheets using Visual Basic for Applications (VBA). The first two courses will teach learners the basics of VBA through the use of dozens of educational screencasts and a series of quizzes and in-application programming assignments. Finally, in Part 3 of the course, learners will complete 3 "real world" and somewhat open ended projects, which are graded through peer review.

Preguntas Frecuentes

¿Cuándo podré acceder a las lecciones y tareas?
El acceso a las clases y las asignaciones depende del tipo de inscripción que tengas. Si tomas un curso en modo de oyente, verás la mayoría de los materiales del curso en forma gratuita. Para acceder a asignaciones calificadas y obtener un certificado, deberás comprar la experiencia de Certificado, ya sea durante o después de participar como oyente. Si no ves la opción de oyente:
es posible que el curso no ofrezca la opción de participar como oyente. En cambio, puedes intentar con una Prueba gratis o postularte para recibir ayuda económica.
Es posible que el curso ofrezca la opción 'Curso completo, sin certificado'. Esta opción te permite ver todos los materiales del curso, enviar las evaluaciones requeridas y obtener una calificación final. También significa que no podrás comprar una experiencia de Certificado.
¿Qué recibiré si me suscribo a este Programa especializado?
Cuando te inscribes en un curso, obtienes acceso a todos los cursos que forman parte del Programa especializado y te darán un Certificado cuando completes el trabajo. Se añadirá tu Certificado electrónico a la página Logros. Desde allí, puedes imprimir tu Certificado o añadirlo a tu perfil de LinkedIn. Si solo quieres leer y visualizar el contenido del curso, puedes auditar el curso sin costo.
¿Hay ayuda económica disponible?
Sí. En ciertos programas de aprendizaje, puedes postularte para recibir ayuda económica o una beca en caso de no poder costear los gastos de la tarifa de inscripción. Si hay ayuda económica o becas disponibles para tu selección de programa de aprendizaje, verás un enlace para postularte en la página de descripción.

¿Tienes más preguntas? Visita el Centro de Ayuda al Alumno.

Desarrolla habilidades para tus empleados e impulsa los resultados de la empresa

Descubre Coursera para Empresas

Excel/VBA for Creative Problem Solving, Part 2

Acerca de este Curso