Building an application for data entry – Inserting VBA ComboBox (ActiveX Control) The VBA ComboBox is called a drop-down list that we can use in Excel. Please note that this is not the same type of list that we can enter in Excel for data correctness. As in the case […]
1. CVar VBA function – Description The CVar VBA function returns a value with the Variant data type . The Variant data type can store both numeric and text data. When using this function, remember that the Variant data type itself is the least efficient data type in VBA. 2. […]
1. CStr VBA function – Description The CStr VBA function returns a value of the String data type . The simplest form of the String data type with a variable number of characters takes up as many bytes in memory as there are characters in a given text string. We […]
1. CSng VBA Function – Description The CSng VBA function returns a value with the Single data type . It is a number between -3.402823e38 and 1.401298e45 . The Single data type is 4 bytes in memory. We usually use functions when we want our data to be more efficient. […]
1. CLngLng VBA function – Description The CLngLng VBA function returns a value of the LongLong data type . It is an integer ranging from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 . The LongLong data type is 8 bytes in memory. It is important that the LongLong data type itself, unlike the Long […]
1. CLng VBA function – Description The CLng VBA function returns a value of the Long data type . It is an integer in the range –2,147,483,648 to 2,147,486,647 . The Long data type is 4 bytes in memory. The function can also be used to convert a number stored […]
1. CInt VBA function – Description The CInt VBA function returns a value of the Integer data type . It is an integer in the range -32768 to 32767 . The Integer data type takes 2 bytes in memory and is therefore very efficient. This way, we can convert low-valued […]
1. CDbl VBA Function – Description The CDbl VBA function returns a value with the Double data type . It is a number between -1.79769313486232e +308 to -4.94065645841247E-324 . The Double data type is 8 bytes in memory. We usually use functions when converting very large floating point numbers. Using […]
1. CDate VBA Function – Description The CDate VBA function returns a value with the Date data type . The Date data type is 8 bytes in memory. The function converts the date and time saved in text form to VBA formatted date and time. The function works like the […]
1. CCur VBA Function – Description The CCur VBA function returns a value with the Currency data type . It is a floating point number formatted as a currency ranging from -922,337,203,477.5808 to 922,337,203,685,477.5807 . The Currency data type is 8 bytes in memory. . Attempting to call a function […]
1. CByte VBA Function – Description The CByte VBA function returns a value of the Byte data type. It is an integer in the range 0-255 . The Byte data type, as its name suggests, occupies a memory space of 1B. This way, we can convert low-valued numbers to store […]
1. CBool VBA Function – Description The CBool VBA function returns a value of the Boolean data type, that is, True or False . We can use it to convert both the result of a logical expression and the number 1 or 0. The CBool function can also be used […]
1. Year VBA function – Description The Year VBA function returns a number representing the year of a given date. So if we want Excel VBA to return to us numbers corresponding to subsequent years, we should use this function. It should be remembered that the date from which we […]
1. WeekdayName VBA Function – Description The WeekdayName VBA function returns the word name of the day of the week from 1 to 7. It is important that the argument for this function is a number, not a date. The function can be combined with the VBA Weekday function or […]
1. Weekday VBA Function – Description The Weekday VBA function returns the word name of the day of the week from numbers from 1 to 7. In the second argument of the function we can define which day of the week we consider as the first, e.g. for Poland it […]
1. VBA TimeValue Function – Description The TimeValue VBA function converts the time stored in text form into a formatted time with the date data type in VBA. This function is also used to extract time values from date and time values. If we would like to convert a text […]
1. TimeSerial VBA Function – Description The TimeSerial VBA function returns the time composed of arguments such as hour , minute , second . If in VBA you have numbers representing hours, minutes, seconds and you want to compose time from them, use the TimeSerial function. In case you want […]
1. Now VBA function – Description The Now VBA function returns the current date and time or the time only. If we want Excel VBA to return the current date, we should use this function. The Date function has no arguments. The time value from the Now function is the […]
1. MonthName VBA Function – Description The MonthName VBA function returns the word name of the month from 1 to 12. It is important that the argument for this function is a number, not a date. The function can be combined with the VBA Month function . The function can […]
1. Month VBA function – Description The Month VBA function returns a number representing the month of a given date. So if we want Excel VBA to return the numbers corresponding to the following months, we should use this function. It should be remembered that the date from which we […]
1. Minute VBA function – Description The Minute VBA function returns a number representing the minutes for a given date and time. So if we want Excel VBA to return the numbers corresponding to the next minutes, we should use this function. It should be remembered that the date and […]
1. Hour VBA function – Description The Hour VBA function returns a number representing the hour for a given date and time. So if we want Excel VBA to return to us numbers corresponding to the following hours, we should use this function for this purpose. Please note that the […]
1. Day VBA function – Description The Day VBA function returns a number that represents the day of the month of a given date. So if we want Excel VBA to return to us numbers corresponding to the next days of the month, we should use this function. It should […]
1. VBA DateValue Function – Description The DateValue VBA function converts a text date into a formatted VBA date. The function is also used to extract the date value itself from a date and time value. If we would like to convert the time saved as text to serial time, […]
1. DateSerial VBA Function – Description The DateSerial VBA function returns a date composed of arguments such as year , month , day . If in VBA you have numbers representing year, month, day and you want to compose a date from them, use the DateSerial function. In case you […]
1. DatePart VBA function – Description The DatePart VBA function returns the individual parts of the date and time depending on the arguments entered. With its help, we can extract elements such as quarter, day of the year, week of the year, as well as standard date elements such as […]
1. DateDiff VBA function – Description The DateDiff VBA function returns the difference between two dates. Depending on what we introduce in the function arguments, we can get the difference in days, months, years, weeks, quarters, hours, minutes, seconds, etc. Monday. In the English nomenclature it will be Sunday. 2. […]
1. DateAdd VBA function – Description DateAdd VBA function is the date or time plus or minus a specified number of time units. So if we want to enlarge the date by e.g. a certain number of days, months and years, we will use this function. We use this function […]
1. Date VBA function – Description The Date VBA function returns the current date. If we want Excel VBA to return the current date, we should use this function. The Date function has no arguments. The date value from the Date function is the system date. You can compare the […]
1. Val VBA function – Description The Val VBA function searches for occurrences of numbers in a string. If a number appears at the beginning of a string, the function returns its value in the form of a number with the Double data type. When a string starts with non-numeric […]
1. UCase VBA function – Description UCase VBA function converts all letters in a string to uppercase. You can compare it to the CAPITAL function in an Excel worksheet. How to use UCASE function in VBA Excel? In the argument of the function, enter a string in quotation marks, or […]
1. Trim VBA Function – Description The Trim VBA function removes spaces from both sides of the text. We use it when we want to remove unnecessary spaces from both the left and right side of a text string. It is worth noting that the function does not remove spaces […]
1. StrReverse VBA Function – Description The StrReverse VBA function returns text with characters in reverse order. If we want to reverse characters in a text string starting from the last one, we should use this function. 2. VBA Replace function – Syntax StrReverse (Expression As String) As String Expression […]
1. StrConv VBA Function – Description The StrConv VBA function converts a text value by specified in the second argument of the parameter. With this function, you can, among other things, convert a string to uppercase like the VBA UCase function . We can convert the string to lowercase like […]
1. StrComp VBA Function – Description The StrComp VBA function compares the length of strings in two texts. Depending on the difference in length, the function returns the values 1, 0 or -1. 2. VBA StrComp Function – Syntax StrComp (String1, String2, [Compare As VbCompareMethod = vbBinaryCompare]) String1 : The […]
1. Str VBA function – Description The VBA Str function converts a numeric value to the String data type . The simplest form of the String data type with a variable number of characters takes up as many bytes in memory as there are characters in a given text string. […]
1. Space VBA function – Description The Space VBA function returns the number of spaces specified in the argument. So, if we want to get a text with only 100 spaces, enter the number 100 in the argument. 2. VBA Space function – Syntax Space (integer) Integer – The number […]
1. RTrim VBA function – Description The RTrim VBA function removes spaces from the right side of text. We use it when we want to remove unnecessary spaces from the right side of a text string. If, however, we want to remove spaces from the left side of the text, […]
1. RIGHT VBA function – Description The RIGHT function in VBA cuts the specified number of characters from the text starting from the right side. It can be compared to the RIGHT function in an Excel worksheet. The function cuts text from other text starting from the right side. For […]
1. Replace VBA function – Description The Replace VBA function is used to replace strings. If we want to replace a word or a fragment of text with another, we use the Replace function. The function has additional arguments with which we can also determine how many occurrences of strings […]
1. MID VBA function – Description The MID VBA function is equivalent to the SUBSTRING or SUBSTR function in other programming languages. How does the MID function work? The VBA MID function cuts text from other text. For example, from the sentence “I’m learning to program in VBA, we can […]
1. LTrim VBA function – Description LTrim VBA function removes spaces from left side of text. We use it when we want to remove unnecessary spaces from the left side of a text string. If we want to remove spaces from the middle of the text, we should use the […]
1. Len VBA function – Description The VBA Len function returns the string length for the given argument. How to use it? All you have to do is enter some text as an argument to the function. The function can be compared to the Length function in other programming languages. […]
1. LEFT VBA function – Description The LEFT function in VBA cuts a specified number of characters from the text starting from the left side. It can be compared to the LEFT function in an Excel worksheet. The function cuts text from other text starting from the left side. For […]
1. LCase VBA function – Description The LCase VBA function converts all letters in a string to lowercase. This can be compared to the LOWER function in an Excel worksheet. How to use LCASE function in VBA Excel? In the argument of the function, enter a string in quotation marks, […]
1. InStrRev VBA function – Description The InStrRev VBA function returns the character position in the string starting from the end. If we want, which position in a given text is occupied by a character or word, we should use this function. This function can also be used to search […]
1. InStr VBA function – Description The InStr VBA function returns the position of a character in a string. If we want which position in a given text a character or word occupies, we should use this function. This function can also be used to search for words in the text […]
1. Chr VBA function – Description The Chr VBA function returns a character based on the position in an ASCII character array. For example, the letter “a” in the ASCII code table is position 97. If we would like to get the position in an array based on a character, […]
1. Asc VBA function – Description The Asc VBA function returns the position of a character in an ASCII character array . For example, the letter “a” is position 97 in the ASCII code table. If we would like to get a letter from the position in an array, we […]
1. Atn VBA function – Description The Atn VBA function returns the arcustangent value. In order to calculate the arcustangent value, we should use this function. For example, arctan with 1 is Pi / 4. 2. VBA Atn function – Syntax 'officeinside.org Atn (Number As Double) As Double Number : […]
1. Tan VBA Function – Description The Tan VBA function returns the tangent value. In order to calculate the value of tngens, we should use this function. For example, the tangent of 0 is equal to 0. 2. VBA Tan function – Syntax 'datatalk.pl Tan (Number As Double) As Double […]
1. Cos VBA function – Description The Cos VBA function returns the cosine value. We should use this function to calculate the cosine value. For example, the cosine of 0 is equal to 1. 2. VBA Cos function – Syntax 'datatalk.pl Cos (Number As Double) As Double Number : The […]
1. Sin VBA function – Description The Sin VBA function returns the sine value. In order to calculate the sine value, we should use this function. For example, the sine of 0 is equal to 0. 2. VBA Sin function – Syntax 'officeinside.org Sin (Number As Double) As Double Number […]
1. Sgn VBA function – Description The Sgn VBA function returns the sign of the number specified in the argument. The function determines the sign, so it checks whether the argument is a negative, positive, or zero. In the case of a negative number, the function returns -1. For added […]
1. Sqr VBA function – Description The Sqr VBA function returns the square root of the argument with the Double data type. With this function we can calculate the root of the second degree. To calculate the root of a greater degree than the second, i.e. the root of the […]
1. Rnd VBA function – Description The Rnd VBA function returns a random number between 0 and 1. The function can be compared to the RAND function in an Excel sheet. We use this function to randomize a number. The operation can be modified in any way to select a number from any range, eg from 0 to […]
1. Round VBA Function – Description The Round VBA function is used to round numbers with decimal places. How can I use the Round function? In place of the first argument of the function, enter the number you want to round. In place of the second argument, enter the number of decimal places to which […]
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 to change data type in VBA Excel? In this chapter, you will learn all convertion functions in Excel VBA . When writing programs in VBA, you will have often a situation where you will have to change the data type. As you probably know, […]
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 VBA TextBox (ActiveX control) VBA textbox has already been introduced to the previous part of VBA course. VBA textbox field is used to enter data and then to process them with VBA code. Inserting a TextBox field into Excel sheet starts with […]
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 the Excel spreadsheet. Its main purpose is to simplify data entry into columns […]
Introduction to ActiveX controls It’s great that you have already reached this part of the VBA Tutorial. VBA ActiveX controls are controls used to 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 of slightly more […]
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 […]
VBA For Next loop – basic informations The VBA For Next Loop is another loop that we will learn. In the simplest words, the loop allows us to enter in advance the number of repetitions of instructions to be done. For example, let’s try to use VBA For loop to display the “For […]
The diagram of Do While Loop The While…Wend loop, which you learned in the previous chapter is the simplest loop in the Excel VBA. The loop that gives you more options is Do Loop. It gives you more possibilities. You can, for example, finish the instructions earlier, by entering the […]
While Wend loop – introduction The VBA While Wend loop is the simplest in construction loop available in VBA. If your code need to be short, not complicated and have a simple structure, it is recommended to use this loop instead of the Do…Loop and For…Next Loop. It gives You similar possibilities. Below […]
Introduction to the loops in Excel VBA Loops are the most powerfull elements in programming languages. Using them, you can automate activities, speed up your programs, do things that you couldn’t do in the Excel datasheet. The loop, as its name suggests, is used to do some operations many times. In VBA Excel, there […]
SELECT CASE instruction – introduction Another instruction next to IF THEN ELSE used in the decision making process by your programs is the SELECT CASE VBA statement. The instruction checks entered value with the defined conditions. On this basis, one scenario is chosen in your decision-making process. After selecting the scenario, the action stored in the block is […]
VBA IF THEN ELSE – What is the conditional statement? In the simplest sense, VBA IF is the equivalent of the Excel IF(), but function used in the VBA code. Imagine that you want your program to make decisions based on the data you enter. Depending on what data you enter at […]
Worksheet functions – How to use the Excel function in VBA Have you ever wondered how you can use the Excel function in VBA? Worksheet functions come with help. As you know, the Excel spreadsheet offers a number of useful functions. The resource of functions that you can use in the VBA code […]
Creating VBA functions defined by user Both Excel and VBA offer you a number of useful functions. But what if you would like to create a VBA function that you will use in Excel? VBA offers you possibility of creating your own functions. These are functions that source code you write yourselve. You can […]
Description of date and time functions. VBA date functions, as the name suggests, are used to manipulate the values related to dates and time. On their basis, you can catch any parameter such as day, month, year, etc. from the given date. You 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’t compare them to the number of functions in an Excel spreadsheet, and these are not the same functions. The use of the sheet functions in VBA code will be described in other chapter. VBA Character functions can […]
Description of math functions in Excel VBA Below I present the names and descriptions of all standard math functions in Excel VBA. As you can see, functions like VBA RND don’t need arguments. Functions like VBA ROUND require 2 arguments – the value and the number of decimal places to […]
Introduction to functions in Excel VBA You have already come to the chapter about VBA functions – Congratulations !!! In VBA we have a large number of useful functions. Using them our programs work in the right way. With their use, you will do mathematical activities, manipulate text data and dates. You can […]
Types of operators in Excel VBA In our scripts we will use both arithmetic expressions and logical expressions. For this purpose, we will be able to use operators in Excel VBA, we can put them into 3 groups: arithmetic logical comparison VBA operators: arithmetic Arithmetic operators serve as the name suggests […]
Introduction to data types in Excel VBA Why is it so important to assign a variable to the correct data type? Your program and its variables use the resources of your computer. If you want to ensure that your program works quickly, choose the types of data that you really […]
Constant values in VBA – what is it? In the previous chapter of the course you learned what are the VBA variables using in your VBA code. The difference between variables and constants in VBA is the values assigned as constant will not change during the launch of your program. If you want your […]
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 variable name that will be interpreted as a variable, and then you can execute any instructions on it. I put an example listed below. 'OfficeInside.Org Sub […]
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 […]
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 something 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 […]