Do you seem difficult to modify or amend certain repetitive tasks that do not add value?
Through this website we'll help you making automations in different programs with few commands without advanced computer skills.
Press and display the lateral menu to see the different possibilities!
Microsoft Excel is a Microsoft Office spreadsheet used to calculate any mathematical and / or logical formula and is often used in financial and accounting tasks.
Visual Basic for Applications uses the programming language Visual Basic applications integrating it into Microsoft Office. This allows simple automations and thus substantially improve the productivity of any company.
The integrated environment called Visual Basic Editor is a separate window with the same look as the Microsoft Office applications.
The programming language is object-oriented, its instructions are based on variables, properties, ... All elements with their properties and characteristics are contained in the objects.
Main features of objects:
Are the characteristics, forms, aspects or attributes of the object.
Example: Refers to the value of cell C1:
Range("C1").Value
It is an action that has effects on an object.
Example: Activate and select cell C1:
Range("C1").Select
It is the result of an action and how the object is behind it. Those actions are produced by the methods.
Examples: Selected a cell, open a book, follow a hyperlink, ...
It is a group of objects contained in another object whose characteristics are common to the component objects.
Example: A book is a collection of spreadsheets.
Basic points that serves to perform virtually all types of programming and improvements (obviously, with more advanced knowledge you can optimize all automations that are displayed here, but that will require a greater effort).
There are two ways to program using Visual Basic in Excel, with the Macro Recorder or Direct Programming Editor.
The macro recorder serves to pass a visual code the actions taken when pressed record without any programming knowledge. In any case, it does not allow everything you can do manually, but it is a very good starting point for programming.
Before using it you have to think exactly what you want to do in order to avoid unnecessary code and a lot of memory.
3) Click "OK" and start with the actions we want to pass to code.
To delete a previously saved macro, click the macro button (or Alt F8) in the "Developer" tab and press "Delete".
To edit and view the code in Visual Basic, click the macros button (or Alt F8) in the "Developer" tab and press "Change". Visual Basic window appears and between "Sub Macroname ()" and "End Sub" is the code generated by the actions taken during the recording of the macro.
Through direct programming in Visual Basic editor you can do more automations than with the Macro Recorder but you need to have a more advanced knowledge.
Here is where all the code of our program is (made by the Macro Recorder or directly).
We will not get to explain each of the parts of this window, we just have to memorize that we have to put the code in a "module" that can be executed anywhere in the book and not just on one sheet. By clicking 2 times on a module, its code window appears.
Within the modules are put procedures. There are several types but the most used are Function and Sub.
This procedure usually return a value and is used to create a function in Excel as "SUM, VLOOKUP, MAX, ...". The syntax is:
Function namefunction ()
…code…
End Function
This other procedure can return one or more values and is the most used for programming. The syntax is:
Sub name ()
…code…
End Sub
On this website we will see only the most useful commands. With them you can make many automations without having to entry into other more complicated or spend much time in training.
With this command appears a window on the screen with the text we want.
You can use the & operator to concatenate several texts or values of variables.
Example:
Sub ExMsgBox()
MsgBox("Proof that today " & Date & " works " & " very well.")
End Sub
This command is used to get the value that the user enters on the keyboard. For this you can assign to a variable that will be used later in the program as we want.
Example:
Sub ExInputBox()
variableexample = InputBox("Enter your name")
MsgBox("Your name is " & variableexample & ".")
End Sub
This command can modify many elements of a cell or range of cells (values, format, source, ...). Of these, the most common is to use or modify the value and is done with the syntax: Range("cell or range").Value.
Example:
Sub ExRange()
Range("A1").Value = 200
Range("A2:B3").Value = 500
End Sub
.Activate is also very useful to activate the cell or .Select to select it. In both cases you position on the cell or range of cells.
Tip: To find other applicable commands such as color, font, underline, ... you can use the macro recorder and by performing certain actions we will know the code.
With this command you can modify a particular cell. It is similar to Range but only for a cell and is indicated by the row number and column number.
Example:
Sub ExCells()
Cells(1,1).Value = 200
End Sub
This command indicates what is selected and with attributes can perform multiple operations. Apart from .Value of the above cases, is usually used .Clear to delete the content, padding, format, ... of the cells found in this selection.
Example:
Sub ExSelection()
Range("C2:D4").Value = 200
Range("C2:D4").Select
Selection.Clear
End Sub
This perhaps is the most widely used of all. The cell is that is active and with the above (and others) attributes can also perform multiple operations. The difference with Selection is that in this case only referece to a single cell. In addition to the above attributes, .Offset is used to move a certain number of rows and columns (especially useful for loops as discussed below).
Example:
Sub ExActiveCell()
Range("C2").Activate
ActiveCell.Offset (1, 2)
End Sub
This command allows you to jump to another part of the procedure without executing the code between it. To do this we call a "flag" whose name we chose. It's pretty good for use in loops and breaks under a given condition without having to think much.
Example: Jump from D2 to Offset without passing E2 or F2:
Sub ExGoTo()
Range("C2").Activate
Range("D2").Activate
GoTo jump
Range("E2").Activate
Range("F2").Activate
jump:
ActiveCell.Offset (1, 2)
End Sub
This command returns the current system date.
Example:
Sub ExDate()
MsgBox ("Today is " & Date & ".")
End Sub
This command capitalizes all letters in a text. Comes in handy when you do not know if the user has put a text in lowercase, uppercase or both and you have to compare it to something programmed (Ex. YES, yes, Yes)
Example:
Sub ExUcase()
variable1 = InputBox ("Put a text")
variable1 = Ucase(variable1)
MsgBox(variable1)
End Sub
This command converts a variable or argument in a number. It is useful to make sure that what has been captured is a number. If the argument indicated is a text, it assign a 0.
Example:
Sub ExVal()
variable1 = Inputbox("Indicates any number")
Val(variable1)
MsgBox(variable1)
End Sub
Checking or unchecking this command we can make that the screen is updated with each command code or not. It is useful to deactivate for faster execution in many loops or long codes and also to avoid the typical screen blink.
Example:
Sub ExScreenUpdating ()
Application.ScreenUpdating = False
Range("A1").Value = 200
Range("A2:B3").Value = 500
Range("B1").Value = 200
Range("B2:D3").Value = 500
Range("M1").Value = 200
Range("N2:S3").Value = 500
Application.ScreenUpdating = True
End Sub
Activating or deactivating this command we can make windows with warnings appear during the execution of the macro or not.
Example:
Sub EjDisplayFileAlerts()
Application.DisplayAlerts = False
...code...
Application.DisplayAlerts = True
End Sub
Activating or deactivating this command we can
ask for the update of external links or not.
Example:
Sub EjAskToUpdateLinks()
Application.AskToUpdateLinks = False
...code...
Application.AskToUpdateLinks = True
End Sub
Here we will highlight only With…EndWith.
Through this control structure we can make a few runs on an object without having to constantly call. It is made by With ... End With.
Example: Make a few changes on the cell C2 calling only once:
Sub ExWith()
Range("C2").Select
With Selection
.Value = 200
.Interior.Pattern = xlSolid
.Interior.ColorIndex = 4
End With
End Sub
Of which there are the most used is certainly If…Then…Else…Endif.
This structure allows to evaluate a condition and, depending on your response (true or false), execute instructions.
If (if) a condition is met then (Then) we execute these actions, if not (Else) run these others End (Endif).
Example:
Sub ExIf()
Range("C2").Activate
If ActiveCell.Value = 200 Then
ActiveCell.Offset(1, 0)
ActiveCell.Value = "The above cell has a value of 200"
Else
ActiveCell.Offset(0, 1)
ActiveCell.Value = "The cell on the left has a value of 200"
Endif
End Sub
Of which there are the most used are For…To…Next and While…Wend.
With this structure a number of known repetitions (loops) is made. This requires to define a variable with a start point and an end point.
For (For) a variable of initial value X to (To) the final value Y does some actions and goes to the next value of the variable (Next)
Ejemplo: Sets the value of x (1,2,3 ... 10) in consecutive row cells from C2:
Sub EjFor()
Range("C2").Activate
For x = 1 To 10
ActiveCell.Value = x
ActiveCell.Offset(1, 0)
Next x
End Sub
Truco: Es posible poner más de un For dentro de otro para por ejemplo hacer matrices de datos.
With this structure repetitions are made (loops) until a certain condition is no longer met. It is important that we make sure that in some moment will fail the condition, otherwise we would enter an infinite loop and the computer will hang for a while (pressing Ctrl + Pause we stop the execution).
While (While) a condition is true, actions are done to end (Wend).
Example: Sets the value of x (1,2,3 ... 10) in consecutive rows cells from C2:
Sub ExWhile()
Range("C2").Activate
x = 1
While x < 10
ActiveCell.Offset(1, 0)
ActiveCell.Value = x + 1
x = x + 1
Wend
End Sub
Objects in Excel (and other programs) have a hierarchy and are:
1. Application. The highest in the hierarchy and serves to call an application of Excel, Word, Outlook, .... You can ignore and put the following.
2. Workbook. It refers to an Excel Workbook.
3. Worksheet. It refers to an Excel Sheet.
4. …here they would go properties or commands we have seen.
They can be chained to call.
Example:
Application.Workbooks("Workbook1.xlsm").Worksheets("Sheet1").Select
With this you can jump to different books, sheets or even applications while running code so we can schedule actions in different places.
Example:
Sub ExObjets ()
Application.Workbooks("Workbook1.xlsm").Worksheets("Sheet1").Activate
Range("A1").Value = 200
Application.Workbooks("Workbook1.xlsm").Worksheets("Sheet2").Activate
Range("B1").Value = 200
End Sub
Relating them seen, the most commonly used commands are ActiveWorkbook, ActiveSheet, .Activate, .Name (to use the name of that item) .Add (to add an element) .Save (to save a book), .Path ( to put the path where the book is).
Here you will find many examples made with full explanations of the above sections. You can copy and paste the code in your visual basic editor to test them.
This example will remove rows that have empty or 0 cells column stock. This is useful for cleaning a large table (could be done with the filter also). So we start from the following table:
Reference | Price | Stock |
as-0002 | 10,00 € | 1000 |
as-0003 | 25,00 € | 100 |
as-0004 | 1,00 € | 5 |
as-0005 | 25,00 € | 200 |
as-0006 | 12,00 € | 250 |
as-0007 | 12,50 € | 325 |
as-0008 | 5,00 € | |
as-0009 | 6,00 € | 6896 |
as-0010 | 8,00 € | 52 |
as-0011 | 9,00 € | 41 |
as-0012 | 10,00 € | 11 |
as-0013 | 10,00 € | 0 |
as-0014 | 10,00 € | 59 |
as-0015 | 50,00 € | 84 |
as-0016 | 30,00 € | 98 |
as-0017 | 21,00 € | 3 |
as-0018 | 33,00 € | 0 |
as-0019 | 25,00 € | 0 |
as-0020 | 25,00 € | 98 |
as-0021 | 25,00 € | 98 |
Note: You can add all rows or columns that you want because the program will go to the last value, whatever it is.
When you run the program it will delete the rows that have the reference as-0008, as-0013, as-0018 and as-0019.
Code:
Sub Deleterows()
'We position ourselves in the first cell:
Range("A1").Activate
'We started the loop until there is no reference:
While ActiveCell.Value <> ""
'Let's column stock:
ActiveCell.Offset(0, 2).Select
'We check whether it is empty or is 0:
If ActiveCell.Value = 0 Or ActiveCell.Value = "" Then
'If so, we remove the line and we go back to the first column:
Selection.Delete
ActiveCell.Offset(0, -2).Activate
Else
'If not, we go to the next row and first column:
ActiveCell.Offset(1, -2).Activate
End If
Wend
End Sub
Result:
Reference | Price | Stock |
as-0002 | 10,00 € | 1000 |
as-0003 | 25,00 € | 100 |
as-0004 | 1,00 € | 5 |
as-0005 | 25,00 € | 200 |
as-0006 | 12,00 € | 250 |
as-0007 | 12,50 € | 325 |
as-0009 | 6,00 € | 6896 |
as-0010 | 8,00 € | 52 |
as-0011 | 9,00 € | 41 |
as-0012 | 10,00 € | 11 |
as-0014 | 10,00 € | 59 |
as-0015 | 50,00 € | 84 |
as-0016 | 30,00 € | 98 |
as-0017 | 21,00 € | 3 |
as-0020 | 25,00 € | 98 |
as-0021 | 25,00 € | 98 |
This example will make multiple copies of a sheet and you're going to give user-defined (using a inputbox) names. In addition, if the sheet indicated does not exist or the number of copies is 0 or a text, it tells you. So we start from the next sheet (Warehouse):
When you run the program, it must ask for the number of copies and the name of each one.
Code:
Sub CopySheets()
'We ask user for the name of the sheet to copy
sheetname = InputBox("Write the name of the sheet that you want copy:")
'Check all the sheets and if doesn´t exist, notify and stopped the program:
sheetexists = False
For i = 1 To Worksheets.Count
If Worksheets(i).Name = sheetname Then
sheetexists = True
End If
Next i
If sheetexists = False Then
MsgBox ("The sheet " & sheetname & " doesn´t exist.")
GoTo end
End If
'We ask the user the number of copies:
numberofcopies = InputBox("Please indicate the number of copies:")
'If the number of copies is not correct, notify and stopped the program:
If Val(numberofcopies) <= 0 Then
MsgBox ("The number of copies entered is incorrect.")
GoTo end
End If
'We ask the user for the names of each copy and make them:
For i = 1 To Val(numberofcopies)
repeat:
copyname = InputBox("Indicates the name of the copy sheet " & i & ":")
'We see if it exists and in this case notify and we ask again:
copysheetexists = False
For j = 1 To Worksheets.Count
If Worksheets(j).Name = copyname Then
copysheetexists = True
End If
Next j
If copysheetexists = True Then
MsgBox ("The sheet " & copyname & " already exists, indicate another name.")
'Ask again:
GoTo repeat
End If
'Select the sheet to copy and do the new one:
Sheets(sheetname).Select
Sheets(sheetname).Copy Before:=Sheets(1)
ActiveSheet.Name = copyname
Next i
end:
End Sub
Result (putting 2 copies with name stockMadrid and stockBarcelona):
In this example, we are going to copy some values from one workbook to another. Let's copy the people who come later than 8:30. To do this we assume that both workbooks are created and the source has the following table:
Person | Entry time | Departure time |
Maria A. | 8:31 | 17:05 |
David S. | 8:15 | 17:22 |
Moises F. | 8:01 | 17:04 |
John W. | 9:05 | 18:15 |
Ian M. | 7:52 | 17:02 |
David F. | 8:33 | 17:48 |
Ana D. | 8:05 | 17:25 |
Martin E. | 8:04 | 18:35 |
Dean M. | 8:25 | 18:00 |
Lucas G. | 8:44 | 18:04 |
When you run the program, it has to ask you the name of the destination workbook.
Code:
Sub Timetable()
'We keep the name of the current book and the name of the initial sheet:
workbooknameorigin = ActiveWorkbook.Name
sheetnameorigin = ActiveSheet.Name
'We ask the user the name of the destination workbook:
workbookdestinationname = InputBox("Write the name of the book (with extension) where you are copying people with higher input schedule 8:30:")
'Check if the destination workbook exists and if so it warns:
workbookexists = False
For i = 1 To Workbooks.Count
If Workbooks(i).Name = workbookdestinationname Then
workbookexists = True
End If
Next i
If workbookexists = False Then
MsgBox ("The destination workbook " & workbookdestinationname & " doesn´t exist.")
GoTo end
End If
'We ask the user for the name of the destination sheet or if he left blank, it creates the same name as the previous:
sheetdestinationname = InputBox("Write the name of the target sheet where you are copying (blank for the same name as the source:")
'Check all the sheets of the destination workbook and if doesn´t exist, creates a new one:
sheetexists = False
Workbooks(workbookdestinationname).Activate
For i = 1 To Workbooks(workbookdestinationname).Worksheets.Count
If Workbooks(workbookdestinationname).Worksheets(i).Name = sheetdestinationname Then
sheetexists = True
End If
Next i
If sheetexists = False Then
Workbooks(workbookdestinationname).Worksheets.Add
ActiveSheet.Name = sheetdestinationname
End If
'We return to the workbook and sheet origin and started copying:
Workbooks(workbooknameorigin).Activate
Worksheets(sheetnameorigin).Activate
'We started copying the header:
Rows(1).Copy
Workbooks(workbookdestinationname).Activate
Worksheets(sheetdestinationname).Activate
Rows(1).PasteSpecial xlPasteAll
'We indicate the next empty row:
destinationrow = 2
'We return to the workbook and sheet origin and continue copying:
Workbooks(workbooknameorigin).Activate
Worksheets(sheetnameorigin).Activate
'We position ourselves in the time column input:
Range("B2").Select
'We started the loop comparative and copy:
While ActiveCell.Value <> ""
'Check if greater than 8:30 (changed to numeric):
If ActiveCell.Value > 0.354166667 Then
'If so, we copy the row:
copyrow = ActiveCell.Row
Rows(copyrow).Copy
Workbooks(workbookdestinationname).Activate
Worksheets(sheetdestinationname).Activate
Rows(destinationrow).PasteSpecial xlPasteAll
'We indicate that the empty row is the next one:
destinationrow = destinationrow + 1
'We return to the workbook and sheet origin:
Workbooks(workbooknameorigin).Activate
Worksheets(sheetnameorigin).Activate
'We go to the next row:
ActiveCell.Offset(1, 0).Activate
Else
'If not, we go to the next row and same column:
ActiveCell.Offset(1, 0).Activate
End If
Wend
end:
End Sub
Result:
Person | Entry time | Departure time |
John W. | 9:05 | 18:15 |
David F. | 8:33 | 17:48 |
Lucas G. | 8:44 | 18:04 |
Press one of the top examples to see it
It is possible to apply VBA to both programs and have them interact. With it the possibilities multiply as we will see.
To do this, it is only necessary to add some more concept to what is in the EXCEL section and that of CATIA V5.
It is necessary that before seeing this section, look first Excel and CATIA V5 because they explain some concepts that are used here.
Here we will see some examples on how to pass Excel data to CATIA.
Note: We always start from a vba program in CATIA.
In this example, 2D points will be created within a sketch determined from the existing X and Y coordinates in an Excel file. We also choose the name of the points.
In this example, the names of the geometry in a sketch will be modified from an Excel file.
Click on one of the above examples to see it
Here we will see some examples on how to pass CATIA V5 data to Excel.
Note: We always start from a vba program in CATIA.
In this example, the coordinates of all the points of a sketch will be obtained and they will be listed in an Excel file.
We must start from CATIA and execute the macro there.
Code:
Sub PointcoordinatestoExcel()
'We indicate that you are going to make the macro:
MsgBox ("Macro to obtain the coordinates of all the points of a CATIA sketch and pass them to Excel." & vbCrLf & "We must have the CATIA file open (it runs on it) and the points should be called starting with Point.")
'We establish CATIA and Excel objects:
Dim CATIA As Object
Dim Excel As Object
'We set a vector variable for the coordinates:
Dim coords(2)
'First we go to Excel, we create the file, we make it visible and we add a new sheet:
Set Excel = CreateObject("Excel.Application")
Excel.Visible = True
Set sheet1 = Excel.ActiveWorkbook.Sheets(1)
sheet1.Name = "Coord CATIA"
'We assign the file name to a variable and then easily access:
fileexcelname = Excel.ActiveWorkbook.Name
'We ask user where we will enter the values of the coordinates:
Set Range = Excel.InputBox("Select the cell of the Excel sheet where you start to put the coordinates obtained from CATIA", 8)
'We take the existing CATIA application, remove the alert notice and initialize the Part and Body objects:
Set CATIA = GetObject(, "CATIA.Application")
CATIA.DisplayFileAlerts = False
Set Part1CATIA = CATIA.ActiveDocument
Set Part = Part1CATIA.Part
Set bodies1 = Part.Bodies
'We ask the user the name of Body and sketch on which we will act:
partname = InputBox("Indicate the name of the Body that contains the Sketch: ", "¿PartBody?", "PartBody")
sketchname = InputBox("Indicate the name of the sketch with the points to obtain its coordinates:", "¿Sketch?", "Sketch.1")
'We chose the Body and sketch indicated by the user::
Set body1 = bodies1.Item(partname)
Set sketches = body1.Sketches
Set sketch1 = sketches.Item(sketchname)
'We also start the geometric elements, in them are the points among others:
Set geometricElements1 = sketch1.GeometricElements
'We see how many geometric elements there are and assign the value to a variable:
geometriesnumber = geometricElements1.Count
'We will sweep all the geometric elements to get their coordinates. We use a variable j to use only the Point:
j = 1
For i = 1 To geometriesnumber
Set point = geometricElements1.Item(i)
'If the name of the element begins with Point then it takes its coordinates and passes them to Excel, rounded to a decimal:
If Left(point.Name, 5) = "Point" Then
point.GetCoordinates coords
pointname = point.Name
Excel.Workbooks(fileexcelname).Activate
Range(j, 1).Value = Round(coords(0), 1)
Range(j, 2).Value = Round(coords(1), 1)
Range(j, 3).Value = pointname
'We add 1 to j for the next point:
j = j + 1
End If
Next i
'We put titles but below the table just in case it is not clear:
Range(j, 1).Value = "Coord X"
Range(j, 2).Value = "Coord Y"
Range(j, 3).Value = "Point Name"
'We indicate that alerts are shown when finished:
CATIA.DisplayFileAlerts = True
End Sub
In this example, the main characteristics of all the holes of a Part (name, thread, diameter, type, ...) will be listed in an Excel file..
We must start from CATIA and execute the macro there.
Code:
Sub Listholefeatures()
'We indicate that you are going to make the macro:
MsgBox ("Macro to list the main characteristics of all the holes in a Part of CATIA and pass them to Excel." & vbCrLf & "We must have the CATIA file open (it runs on it) and the holes should be called starting with Hole.")
'We establish CATIA and Excel objects:
Dim CATIA As Object
Dim Excel As Object
'We establish a vector variable for the coordinates of the holes (X, Y, Z):
Dim coords(3)
'First we go to Excel, we create the file, we make it visible and we add a new sheet:
Set Excel = CreateObject("Excel.Application")
Excel.Visible = True
Set sheet1 = Excel.ActiveWorkbook.Sheets(1)
sheet1.Name = "CATIA HOLES"
'We assign the file name to a variable and then easily access:
fileexcelname = Excel.ActiveWorkbook.Name
'We ask the user where we will enter the values:
Set Rango = Excel.InputBox("Select the cell of the Excel sheet where you start to put the characteristics obtained from CATIA",Type:=8)
'We take the existing CATIA application, remove the alert notice and initialize the Part and Body objects:
Set CATIA = GetObject(, "CATIA.Application")
CATIA.DisplayFileAlerts = False
Set PartCATIA = CATIA.ActiveDocument
Set Part1 = PartCATIA.Part
Set bodies1 = Part1.Bodies
'We will sweep all the bodies of the Part, taking all the forms We use a variable j to use only the Hole then:
j = 1
For Each body In bodies1
Set shapes = body.Shapes
'We put in a variable the name of the body:
holebodyname = "" & body.Name
'We make another sweep over all the ways to look for the holes:
For i = 1 To shapes.Count
Set hole = shapes.Item(i)
'If the element name starts with Hole then it takes its characteristics and passes them to Excel:
If Left(hole.Name, 4) = "Hole" Then
'Coordinates, Name, Type of hole, with or without thread, diameter:
hole.GetOrigin coords
holename = "" & hole.Name
holetype = "" & hole.Type
'We put the type of hole instead of the numerical value of the index:
If holetype = 0 Then
holetype = "Simple"
Else
If holetype = 1 Then
holetype = "Tapered"
Else
If holetype = 2 Then
holetype = "Counterbored"
Else
If holetype = 3 Then
holetype = "Countersunk"
Else
holetype = "Counterdrilled"
End If
End If
End If
End If
holeThreadingMode = hole.ThreadingMode
'We put the type of thread instead of the numerical value of the index:
If holeThreadingMode = 0 Then
holeThreadingMode = "Threaded"
Else
holeThreadingMode = "Not threaded"
End If
holediameter = "" & Round(hole.Diameter.Value, 1)
'If it has thread, depth, metric and pitch:
If holeThreadingMode = "Threaded" Then
holethreaddepth = "" & Round(hole.ThreadDepth.Value, 1)
holemetric = "" & hole.HoleThreadDescription.Value
holepitch = "" & hole.ThreadPitch.Value
Else
holethreaddepth = "---"
holemetric = "---"
holepitch = "---"
End If
'Type of hole completion:
holebottomtype = "" & hole.BottomType
'We passed it to Excel:
Excel.Workbooks(fileexcelname).Activate
Rango(j, 1).Value = holename
Rango(j, 2).Value = Round(coords(0), 1)
Rango(j, 3).Value = Round(coords(1), 1)
Rango(j, 4).Value = Round(coords(2), 1)
Rango(j, 5).Value = holetype
Rango(j, 6).Value = holeThreadingMode
Rango(j, 7).Value = holediameter
Rango(j, 8).Value = holethreaddepth
Rango(j, 9).Value = holemetric
Rango(j, 10).Value = holepitch
Rango(j, 11).Value = holebottomtype
Rango(j, 12).Value = holebodyname
j = j + 1
End If
Next i
Next body
'We put titles but below the table just in case it is not clear:
Rango(j, 1).Value = "Hole name"
Rango(j, 2).Value = "X coord"
Rango(j, 3).Value = "Y coord"
Rango(j, 4).Value = "Z coord"
Rango(j, 5).Value = "Type"
Rango(j, 6).Value = "Thread?"
Rango(j, 7).Value = "Diameter"
Rango(j, 8).Value = "Th depth"
Rango(j, 9).Value = "Th metric"
Rango(j, 10).Value = "Th pitch"
Rango(j, 11).Value = "Bottom"
Rango(j, 12).Value = "Body"
'We indicate that alerts are shown when finished:
CATIA.DisplayFileAlerts = True
End Sub
Click on one of the above examples to see it
CATIA V5 is a Dassault application for a multitude of 3D and 2D design tasks. Here we will focus on the module "Mechanical Design" and more specifically in Sketcher, Part and Product, which are the most common in mechanical design.
Visual Basic for Applications uses the Visual Basic programming language integrating it in Microsoft Office applications and also in other external applications such as CATIA V5. This allows to easy perform automations in these programs and thus, substantially improve the productivity of any company.
In CATIA V5 you have a Visual Basic editor and it is a separate window. It is accessed from the "Tools" tab, by clicking on "Macros" and "Visual Basic Editor".
The programming language is oriented to objects, its instructions are based on variables, properties, ... All elements with their properties and characteristics are contained in the objects.
They are used to store CATIA macros. There are 3 possibilities for this:
1) Folders: vbscript y CATScript
2) Project files: catvba
3) Files: CATParts/CATProducts
It can be created or added by doing the following:
1) Press "Tools", "Macro" and "Macros":
2) Press "Macro Libraries":
3) Press "Add Existing library" to add an existing one or "Create New library":
4) Select the folder where the library is in case you add or indicate where you want to create the folder that will contain this new library
There are two ways to program using Visual Basic in CATIA V5, with Macro recorder or with the Direct Programming Editor
The macro recorder serves to pass a visual code the actions taken when pressed record without any programming knowledge. In any case, it does not allow everything you can do manually, but it is a very good starting point for programming.
Before using it you have to think exactly what you want to do in order to avoid unnecessary code and a lot of memory.
To modify and view the code in Visual Basic, click on "Tools", "Macros", click on "Edit":
The corresponding Editor window appears (Visual Basic or script) and between "Sub nameofthemacro()" and "End Sub" is the code generated by the actions performed during the recording of the macro.
Through direct programming in Visual Basic editor you can do more automations than with the Macro Recorder but you need to have a more advanced knowledge.
This is where all the code of our program is located (whether it has been done through the Macro Recorder in catvba format, or directly).
We are not going to explain each of the parts of this window, we just have to keep in mind that we have to put the code in a "module" so that it can be executed anywhere in CATIA. Clicking twice on a module displays the window with its code.
The procedures are included in the modules. There are several types but the most used for CATIA is Sub.
This procedure can return one or several values and is the most used to program. The syntax is:
Sub name()
…code…
End Sub
On this web page we will see only the most useful commands. With them you can perform many automations without having to enter other more complicated or spend much time to train.
With this command you create a window that appears on the screen with the text you want.
The & operator can be used to concatenate multiple texts or variable values.
Example:
Sub ExMsgBox()
MsgBox("Today is " & Date & ".")
End Sub
This command serves to take the value that the user enters through the keyboard. For this, it can be assigned to a variable that will later be used in the program as we wish.
Example:
Sub ExInputBox()
varexample = InputBox("Insert your name")
MsgBox("Your name is " & varexample & ".")
End Sub
This command indicates what is selected and with the attributes multiple operations can be performed.
Example:
Sub ExSelection()
Set Selection1 = PartCATIA.Selection
Selection1.Clear
End Sub
This command allows you to jump to another part of the procedure without executing what is in between. To do this, we call a "flag" whose name we choose. It is good enough to use it in loops and perform interruptions under a certain condition without having to think much.
Example: Jump from Forms1 to Formas2 without going through numeroshapes:
Sub ExGoTo()
Set Forms1 = body.Shapes
GoTo jump
numeroshapes = Forms1.Count
jump:
Set Forms2 = body.Shapes
End Sub
This command returns the current system date.
Example:
Sub ExDate()
MsgBox ("Today is " & Date & ".")
End Sub
This command converts a variable or argument to a number. It is useful to make sure that what has been captured is a number. If the indicated argument is a text it assigns it a 0.
Example:
Sub ExVal()
var1 = Inputbox("Insert a number")
Val(var1)
MsgBox(var1)
End Sub
By activating or deactivating this command we can make the screen update with each code command or not. It is useful to deactivate it so that the execution is faster in long codes or with many loops and also avoids the typical blinking of the screen.
Example:
Sub ExScreenUpdating ()
CATIA.ScreenUpdating = False
...code...
CATIA.ScreenUpdating = True
End Sub
Activating or deactivating this command we can make windows appear with warnings during the execution of the macro or not.
Example:
Sub ExDisplayFileAlerts()
CATIA.DisplayFileAlerts = False
...code...
CATIA.DisplayFileAlerts = True
End Sub
Here we are only going to highlight With…EndWith.
Through this control structure we can make a few executions on an object without having to constantly call it. It is done through With…End With.
Example:
Sub ExWith()
Set Holelimit= hole.BottomLimit
With Holelimit
.Dimension.Value = 11
.LimitMode = catOffsetLimit
End With
End Sub
Of which there is the most employed is undoubtedly If…Then…Else…Endif.
This structure allows to evaluate a condition and, according to its response (true or false), execute some instructions or others.
If (if) a condition is fulfilled then (Then) we execute these actions, but (Else) we execute these other End (Endif).
Example:
Sub ExIf()
drawingstate = 0
If drawingstate = 1 Then
drawingstate = 0
Else
drawingstate = 1
End If
End Sub
Of those that are the most used are For Each…Next, For…To…Next y While…Wend.
With this structure a number of repetitions (loops) are made that depend on the number of objects in a certain collection.
For each (For Each) variable within a collection, make some actions and go to the next value of the variable (Next).
Example:
Sub ExForEach()
Set Bodies1= Part.Bodies
For Each body In Bodies1
Set Forms = body.Shapes
numeroshapes = Forms.Count
Next body
End Sub
With this structure a number of known repetitions (loops) are performed. To do this, a variable is defined with a starting point and an end point.
To (For) a variable of initial value X To (To) the final value AND make some actions and Go to the next value of the variable (Next)
Example:
Sub ExFor()
Set Bodies1= Part.Bodies
Set Forms = Bodies1.Shapes
numeroshapes = Forms.Count
For I = 1 To numeroshapes
Set hole = Forms.Item("Hole." & I & "")
hole.Diameter.Value = 10
Next I
End Sub
Tip: It is possible to put more than one For into another for example to make data matrices.
With this structure repetitions (loops) are performed until a certain condition is no longer met. It is important that we make sure that at some point the condition will fail, otherwise we would enter an infinite loop and the computer will hang for a while (by pressing Ctrl + Pause we stop the execution).
While (While) a condition is true, it performs actions (Wend).
Example:
Sub ExWhile()
counter = 0
While counter < 10
counter = counter + 1
Wend
Msgbox("Counter value: " & counter & ".")
End Sub
The objects in Excel (and other programs) have a hierarchy and are:
1. Application. The highest in the hierarchy and worth to call an application of CATIA, Excel, Word, Outlook, .... You can ignore and put the following.
2. Product. Refers to a set of parts (Product).
3. Part. It refers to a piece (Part).
4. …here would go properties or commands that we have seen.
You can go chaining to call them.
Example:
Application.Product("Example.CATProduct").Part("Part1.CATPArt").Select
Here you will find many examples made with full explanations of the above sections. You can copy and paste the code in your visual basic editor to test them:
In this example, all the elements of a product at the first level will be counted and listed.
The Product must be active to work.
Code:
Sub Countandlistfirstlevelelements()
'We indicate that no alerts are shown during execution:
CATIA.DisplayFileAlerts = False
'We start the active CATIA Document:
Set ProdDoc = CATIA.ActiveDocument
'We start the Product:
Set Prod = ProdDoc.Product
'We count the total number of elements and put them in a variable:
numberofproducts = Prod.Products.Count
'We create another text variable to then enter the list of names:
nameofproduct = ""
'We will sweep all the elements of the product to get their names:
For j = 1 To numberofproducts
'We start each element (Item):
Set Elem = Prod.Products.Item(j)
'If the text variable was empty, put the name directly, but add a comma to make it look better:
If nameofproduct = "" Then
nameofproduct = Elem.Name
Else
nameofproduct = nameofproduct & ", " & Elem.Name
End If
'We go to the next element, repeating the loop if the j is less than numberofproducts:
Next j
'After finishing the For loop, we create and show the message with everything together, total number and names of elements:
MsgBox ("Total number of elements within this product: " & numberofproducts & "." & vbCrLf & "Their names are: " & nameofproduct & ".")
'We indicate that alerts are shown when finished:
CATIA.DisplayFileAlerts = True
End Sub
In this example, all holes in any body of a piece (Part) whose name begins with Hole will be eliminated. and being the first Hole.1.
Leave your sketch of empty positions and patterns if there are any.
Initially:
Code:
Sub Deleteholes()
'We indicate that no alerts are shown during execution:
CATIA.DisplayFileAlerts = False
'We start the active CATIA Document:
Set PartCATIA = CATIA.ActiveDocument
'We start the selection:
Set Selection1 = PartCATIA.Selection
'We make sure that any element of the selection is deleted:
Selection1.Clear
'We start the Part:
Set Part1= PartCATIA.Part
'We start the bodies:
Set Bodies1 = Part1.Bodies
'We will sweep all the bodies of the piece to eliminate holes in all of them:
For Each body In Bodies1
'We begin the forms that body has:
Set Forms = body.Shapes
'We create a variable with the total number of forms that the body has:
numbershapes = Forms.Count
'We sweep all the shapes searching holes:
For I = 1 To numbershapes
'We start the hole:
Set hole = Forms.Item("Hole." & I & "")
'If it does not exist, we move on to the next order without stopping the execution:
On Error Resume Next
'We add it to the selection if it exists, if it does not exist it adds emptiness so it is also valid:
Selection1.Add hole
'We move to the following form, repeating the loop if the I is less than numbershapes:
Next I
'After finishing the For loop, we eliminate all the selection of that body (holes):
Selection1.Delete
'We move to the next body if there is:
Next body
'We indicate that alerts are shown when finished:
CATIA.DisplayFileAlerts = True
End Sub
Result:
In this example, all roundings and chamfers of any body of a piece (Part) whose name begins with Edge will be eliminated. or Chamfer. and being the first Edge.1 and Chamfer.1 respectively.
Initially:
Code:
Sub Deleteroundingschamfer()
'We indicate that no alerts are shown during execution:
CATIA.DisplayFileAlerts = False
'We start the active CATIA Document:
Set PartCATIA = CATIA.ActiveDocument
'We start the selection:
Set Selection1 = PartCATIA.Selection
'We make sure that any element of the selection is deleted:
Selection1.Clear
'We start the Part:
Set Part1 = PartCATIA.Part
'We start the bodies:
Set Bodies1 = Part1.Bodies
'We will sweep all the bodies of the piece to eliminate in all of them the roundings and chamfers:
For Each body In Bodies1
'We begin the forms that body has:
Set Forms = body.Shapes
'We create a variable with the total number of forms that the body has:
numbershapes = Forms.Count
'First we will sweep all shapes looking for rounding:
For I = 1 To numbershapes
'We begin the rounding:
Set egde1 = Forms.Item("EdgeFillet." & I & "")
'If it does not exist, we move on to the next order without stopping the execution:
On Error Resume Next
'We add it to the selection if it exists, if it does not exist it adds emptiness so it is also valid:
Selection1.Add egde1
'We move to the following form, repeating the loop if the I is less than numbershapes:
Next I
'After finishing the For loop, we eliminate all the selection of that body (rounding):
Selection1.Delete
'Now we sweep all the shapes looking for the chamfers:
For I = 1 To numbershapes
'We start the chamfer:
Set Chamfer1 = Forms.Item("Chamfer." & I & "")
'If it does not exist, we move on to the next order without stopping the execution:
On Error Resume Next
'We add it to the selection if it exists, if it does not exist it adds emptiness so it is also valid:
Selection1.Add Chamfer1
'We move to the following form, repeating the loop if the I is less than numeroshapes:
Next I
'After finishing the For loop, we eliminate all the selection of that body (chamfers):
Selection1.Delete
'We move to the next body if there is:
Next body
'We indicate that alerts are shown when finished:
CATIA.DisplayFileAlerts = True
End Sub
Result:
In this example, all reference planes of the part, that is, planes XY, YZ and ZX, are hidden or displayed according to their status:
Initially:
Code:
Sub HideShowreferenceplanes()
'We indicate that no alerts are shown during execution:
CATIA.DisplayFileAlerts = False
'We start the active CATIA Document:
Set PartCATIA = CATIA.ActiveDocument
'We start the selection:
Set Selection1 = PartCATIA.Selection
'We make sure that any element of the selection is deleted:
Selection1.Clear
'We start the Part:
Set Part1 = PartCATIA.Part
'We start the visual properties of the selection to then see the state and hide:
Set VisProp = Selection1.VisProperties
'We start the elements of origin (reference planes):
Set Origelem = Part1.OriginElements
'We create variables with each reference plane to facilitate the subsequent management:
Set Plane1 = Origelem.PlaneXY
Set Plane2 = Origelem.PlaneYZ
Set Plane3 = Origelem.PlaneZX
'We add them to the selection:
Selection1.Add Plane1
Selection1.Add Plane2
Selection1.Add Plane3
'We assign to a variable stateplanes if planes are hidden or visible:
VisProp.GetShow stateplanes
'We change the state to the contrary by means of an If:
If stateplanes = 1 Then
VisProp.SetShow 0
Else
VisProp.SetShow 1
End If
'We clean the selection:
Selection1.Clear
'We indicate that alerts are shown when finished:
CATIA.DisplayFileAlerts = True
End Sub
Result:
In this example, all the hole diameters of any body of a piece (Part) whose name begins with Hole will be changed to the same value. and being the first Hole.1.
Initially:
Code:
Sub Equalholediameters()
'We indicate that no alerts are shown during execution:
CATIA.DisplayFileAlerts = False
'We start the active CATIA Document:
Set PartCATIA = CATIA.ActiveDocument
'We start the Part:
Set Part1 = PartCATIA.Part
'We start the bodies:
Set Bodies1 = Part1.Bodies
'We ask the user for the value and assign it to a variable:
newdiamtring = InputBox("Insert the new diameter for all the holes: ", "Diameter modification")
'We convert the previous variable to a number and put it in another:
newdiam = Val(newdiamtring)
'If the new diameter is 0 or negative, notify and for the macro:
If newdiam <= 0 Then
answer = MsgBox("Incorrect new diameter: " & newdiamtring, vbCritical)
'We indicate that alerts are shown as we are going to stop the macro:
CATIA.DisplayFileAlerts = True
'We stop the macro completely, not just the If:
End
'If the new diameter is valid, we will sweep all the bodies of the piece and its forms:
Else
For Each body In Bodies1
'We begin the forms that body has:
Set Forms = body.Shapes
'We create a variable with the total number of forms that the body has:
numbershapes = Forms.Count
'We will sweep all shapes looking for holes:
For I = 1 To numbershapes
'We start the hole:
Set hole = Forms.Item("Hole." & I & "")
'If it does not exist, we move to the following order without stopping the execution:
On Error Resume Next
'We start the diameter:
Set Diameter1 = hole.Diameter
'We change its value to the new one:
Diameter1.Value = newdiam
'We move to the following form, repeating the loop if the I is less than numbershapes:
Next I
'We move to the next body if there is:
Next body
'We update the part:
Part1.Update
End If
'We indicate that alerts are shown when finished:
CATIA.DisplayFileAlerts = True
End Sub
Result:
Click on one of the above examples to see it
If you need me to help you do some programming, you just have to send me an email. Contact -  
Further, if you want help me to support and improve this page you can give me a donation of bitcoins or dogecoins in this address:
Bitcoin (BTC): 3D9938F7mNZzsMAZ8Lwnqgca5QRYSnGdVn
Dogecoin (DOGE): DSAv4Ri4eqN7YseEcicC5akZGLC2nNa9BA
Any help is welcome. And do not forget to send me an email to give me the opportunity to thank you!!