1. CMD Shell in VBA Excel – what it is and how to use it In this article you will learn how to control other Windows programs from Microsoft Excel. If you are trying to perform any action in Windows using Excel, all of these actions are used by the Windows […]
1. VBA tables – information for advanced users I assume that if you are reading this article you have previously read the basic information about VBA tables , which I wrote about in this article: VBA arrays. In the article you are reading you will learn more advanced information about VBA tables. These are functions that you […]
VBA Error handling – How to use it? In this article you will learn what VBA error handling is and how to use it efficiently. In VBA, as in any other program writing errors appear. They may result, among others from the incorrect syntax or logic of the program. Errors that may occur in […]
VBA conversion functions – Description. How do you change the data type in VBA Excel? In this chapter you will learn all the conversion functions in Excel VBA . When writing programs in VBA, you will often encounter a situation in which you will have to change the format of the data. The direct cause […]
What are tables in VBA Excel – VBA Arrays In this chapter of the VBA course you will learn what VBA tables are and how to use them in data analysis. VBA tables are containersfor storing data of the same type in an organized manner. We can refer to each element of an array using its indexed address. Arrays […]
How do you write an Excel add-in in VBA? Creating a simple add-on in VBA Excel is primarily about writing a good VBA macro code. In this chapter you will learn how to install such a macro as an Excel add-in. The add-on will consist of two macros: Macro 1 – opening […]
How do you insert the ComboBox combo box into the UserForm form? The combo box is already known to us. We got to know them while creating the application for entering data . Due to the fact that the field inserted in the UserForm form is programmed in a different place, we will create […]
Insert MultiPage tabs into the UserForm form MultiPage Bookmarks are pages of our application, between which the user will be able to pass. On each page you will see other elements and content of the application. To insert bookmarks into our UserForm form, we should choose the MultiPage control from the ToolBox. Our […]
How to build an application in Excel VBA using the UserForm form In the last part of the course we will deal with the creation of a short application using the form. As in the case of the previous application, we should write down the basic requirements based on which our […]
What is VBA UserForm The UserForm form is a form that works under Excel that allows us to create visually developed applications. You can add more types of controls to the UserForm form than to the data sheet. From the controls of the UserForm form, we can distinguish primarily: Label label TextBox […]
Building an application for entering data – Inserting Labels – Label (ActiveX Control) The label Label is already known to us and should not require a broader discussion. How do you insert a Label Label? From the ActiveX controls in the Developer tab, select the Label control. Next, we put 3 labels next […]
Construction of the application for entering data – Inserting the Knob Button – SpinButton (ActiveX Control) The next ActiveX control we will know is the knob button control. We will use it for navigation, and precisely move the rows of the sheet up and down. Our goal is to click on the […]
Building an application for entering data – Inserting a Command Button – CommandButton (ActiveX Control) The command button is already known to us. In the simplest form, it will be used to run written scripts. The command button can be embedded in both the Excel spreadsheet and the UserForm form. How do I […]
Building an application for entering data – Inserting CheckBox check boxes (ActiveX control) How do I enter the CheckBox checkbox? We embed them in Excel in the same way as the option buttons . The important thing is that unlike the option button, we can simultaneously select several CheckBoxes. The CheckBox selected by the user […]
Building an application for entering data – Inserting an Option Button Option (ActiveX Control) We would like the data on gender to be entered in the “I” column of our data table, which is the value of Woman or Man. In this case, we should construct our application in such a […]
Building an application for entering data – Inserting a ListBox list box (ActiveX control) The list box, unlike the combo box, allows us to select more than one selection option at the same time. How do I insert a ListBox list field? As in the case of previous controls, we enter the Developer […]
Building an application for entering data – How to insert a TextBox (ActiveX control) The text field has already been introduced to the previous part of our course. The field is primarily for us to enter data and then to process them in the VB editor. Inserting a TextBox text field starts […]
Excel VBA training: Building an application for entering data Let’s do an example that uses all of the known ActiveX controls. Our goal will be to build a short application for entering personal data. The application will be based on an Excel spreadsheet. Its main purpose is to simplify data entry into columns […]
Introduction to ActiveX controls It’s nice that you have already reached this part of the training :). VBA ActiveX controls are controls by means of which we can visualize our applications in Excel. ActiveX controls available in the Developer tab are used primarily to build simple applications running under Excel. For the construction […]
What is the For Each Next loop in VBA? In this article you will learn what the For Each Next loop is and how to use it in Excel VBA. The For Each Next loop is an extension of the VBA For loop . The difference between the two types of loops is that For Each we can […]
For Next VBA loop – basic information The For Next VBA loop is another loop that we will meet. In the simplest form, the loop allows us to enter in advance the number of repetitions of instructions to be performed. For example, let’s try to use our loop to display the “Example For […]
The diagram of Do While Loop The While loop … Wend , which we met in the previous chapter is the simplest loop in the Excel VBA. The loop that gives us more options is Do … Loop. It gives us many more possibilities. We can, for example, finish the instructions earlier, by entering the exit condition. Our […]
While Wend loop – introduction The While Wend loop is the simplest in its construction loop available in VBA. If our code is to be short, uncomplicated and have a simple structure, it is recommended to use this loop instead of the Do … Loop loop. It gives us similar possibilities. Below is […]
Introduction to the loops in Excel VBA Loops are the strongest element of programming languages. It is through them that we can automate activities, speed up the work of our programs, do things that we could not do in the Excel data sheet. The loop itself, as its name suggests, is used […]
SELECT CASE instruction – introduction Another instruction next to IF THEN ELSE used in the decision making process by our program is the SELECT CASE VBA statement . The instruction checks whether the instruction entered in the instructions is in accordance with the conditions stated in the instructions. On this basis, one scenario is chosen in our decision-making […]
What is the conditional statement If Then Else VBA In the simplest sense, this is the equivalent of the ” If () ” function used in the VBA code. Imagine that we want our program to make decisions based on the data we enter. Depending on what data you enter at the beginning, we […]
Worksheet functions – How to use the Excel function in VBA Have you ever wondered how we can use the Excel function in VBA? Sheetfed functions come with help. As we know, the Excel spreadsheet offers us a number of useful functions. The resource of functions that we can use in the VBA […]
Creating own function defined by user Both Excel and VBA offer us a number of useful functions. But what if we would like to create a function that we will use in Excel? The possibility of creating so-called user functions. These are functions whose source code we write ourselves. We can use them in both […]
Description of date and time functions. The VBA date functions as per the name indicate are used to manipulate values related to dates and time. On their basis, we can receive any parameter from the given date, such as day, month, year, etc. We can also subtract two dates from each […]
VBA character functions in Excel The Excel VBA offers us a number of useful character functions. We can not compare them to the number of functions in an Excel spreadsheet, and most importantly, these are not the same functions. The use of the sheet functions in our VBA code will be described in […]
Description of mathematical functions in Excel VBA Below are the names and descriptions of all standard math functions in Excel VBA. As you can see functions such as RND () do not require arguments. Functions such as ROUND () require 2 arguments – the value and the number of decimal places to which we round our […]
Introduction to functions in Excel VBA You have already come to the chapter about functions – Congratulations, keep it up !!! In VBA we have a number of useful functions. It is through them that our programs will work in the right way. With their help, we will perform mathematical activities, manipulate text […]
Types of operators in Excel VBA In our scripts we will use both arithmetic expressions and use logical expressions. For this purpose, we will be able to use operators in Excel VBA, we can divide them into 3 groups: arithmetic logic comparison. VBA operators: arithmetic Arithmetic operators serve as the name […]
Introduction to data types Why is it so important to assign a variable to the correct data type? Our program and its variables use the resources of our computer. If we want to ensure that our program works quickly and efficiently, we choose the types of data that we really want to use. For example, […]
What are constant values in VBA? In the previous chapter of the training you learned what are the VBA variables appearing in our VBA code. The difference between variables and constants of VBA is such that the values assigned to constants will not change during the launch of our program. If we want our […]
Introduction to VBA variables. In VBA, as in other programming languages, we work with variables. The VBA language does not require declaring variables. You can, for example enter a variable name that will be interpreted as a variable, and then execute any instructions on it. I put an example listed below. [crayon-5f07b814a12cb690937104/] The result […]
Formatting cells and fonts in VBA – introduction Cells as well as other elements of the Excel spreadsheet can be formatted from the VBA code level. The formatting itself can be done in 2 ways. The first way is to set each format using a separate code block. You can also use the With clause to […]
InputBox window – entering data into VBA It’s great, that you have already reached this part of VBA tutorial. After the VBA MsgBox window you will learn how to work with window for entering data into our VB code. Like the MsgBox window, this window has many additional parameters. Let’s […]
VBA MsgBox – data presentation in window You have lerned basics of VBA MsgBox window while writing your first program here: VBA first program. This window is one of many possibilities to present data using VBA code in Excel. It can be use to display messages, the program results etc. Below […]
1. How to use VBA in Excel worksheet It’s great that you’ve already reached this part of the VBA Excel training. In this chapter we will learn in detail the possibilities offered by the VBA in the Excel worksheet. If you are wondering how to write a program that will […]
1. What is macro in Excel In this part of VBA course you will learn how to record and run macros under Excel program. As you already know Macros in Excel are programs which are running under Excel. Each of those programs are based on code, which were written in […]
1. How to write first program in Excel VBA If you already know what macros are and what the VBA is, it’s time to write your first program in VBA. First of all we would like to fill A1 cell in the Excel worksheet with message Welcome to VBA! To […]
1. VisualBasic Editor – Introduction The VisualBasic editor will be the tool that you will use in this course. In order to take full advantage of the VBA option on your computer, you should start with: Unlocking the Developer tab in Excel Enable macro operations in Excel 2. How to […]
Why do we use VBA? You can use the VBA code in Excel for example to: Create useful macros Make own functions, ie. functions that Excel doesn’t have Create Excel add-ons Use programs not related to Excel Automate your work. Imagine, your work is done and you can do more […]
1. VBA VLookUp – How to match rows? Do you want to match Excel records in VBA? VBA in Excel gives you the possibility to use the sheet functions. VBA VLookup is very important spreadsheet function. To use vLookUp function, refer to the worksheet functions using command Application.WorksheetFunction.VLookup. Let’s create two simple tables in Excel […]
1. How to extract day, month, year, weekday and quarter from date in Excel? If you are reading this article, you would probably like to use the date and time function in Excel. Excel has built-in sheet functions to help you calculate and extract date parts from the date. At the beginning, you […]
1. Search for the last row number in the Excel table using VBA. Often in VBA we need to add another row or data record into the Excel table. To do this, it is usually necessary to find the location of the last non-empty row. To find the last completed record in the […]
1. Log VBA function – Description The Log VBA function returns value of the natural logarithm. Natural logarithm is based on e number ~ 2.72 (Euler’s number). To calculate natural logarithm value, you should use this function. For example, the value of natural logarithm from 1 is 0. 2. VBA Log […]
1. Int VBA function – Description The Int VBA function is used to round numbers that have decimal places to integers. The Int function, in comparision to the Round function, does not require entering additional arguments. The result will always be an integer. An important information is that for negative numbers, the VBA […]
1. Fix VBA function – Description The Fix VBA function is used to round numbers, that have decimal places to integers. The Fix function, in comparision to the Round function, does not require entering additional arguments. The result will always be an integer. Important information is, that for negative numbers, the VBA […]
1. Exp VBA function – Description The Exp VBA function returns an exponential value of a number. To calculate exponential value, you should use this function. For example, exponential value from 1 is the Euler’s number ~ 2.72. 2. VBA Exp function – Syntax [crayon-5f07b814ba7ed187798140/] Number : A numeric value […]
1. Abs VBA function – Description The Abs VBA function returns the absolute value of a number. If you use this function for positive numbers it will return unchanged argument. The function used for negative numbers will return a positive value. If you want to calculate the absolute value in VBA, […]