0

I need your help in using CATIA correctly.

I'm currently tasked with creating a "support" Part for a Mould where a capscrew must be inserted (regardless of its length) a maximum of 15mm. The length of the inserted capscrew depends on a plate that is located above the support part. In Excel I have created several formulas that determine which capscrew must be used but I do not know how to use the excel formulas in VBA. Is there any way to use excel formulas or even spreadsheets in VBA?

'declaration of X as "Height of Plate 9"
Dim X As Integer
X = length1.Value

'declaration of Y as the result for the optimal length of the CapScrew to be used
Dim Y As Integer
Y = 0

 'Formula for the length of the CapScrew to be used
If (designTable1.Configuration <= 15) = True Then
    Y = X - 10 - 1 + 15
    designTable2.Configuration = Y
Else
    Y = X - 12 - 1 + 15
    designTable2.Configuration = Y
End If
Community
  • 1
  • 1
Castella
  • 3
  • 1
  • 7
  • 3
    Welcome. Please use your favorite search engine with words "vba excel create formula". There are many guides and examples, for [example](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-formula-property-excel) from the Microsoft. If you run into specific programming problem during this, then return here and describe it. – miroxlav Aug 14 '17 at 22:59
  • There is a VBA command to use excel formulas but alas I do not know what the command is. Google will help you find the command. You could also try recording a macro and typing in a formula and see what the macro comes up with. – Forward Ed Aug 15 '17 at 04:40
  • Do you mean you want to use excel formulas in CATIA VBA code? – simpLE MAn Aug 18 '17 at 15:57
  • @simpLE MAn Initially i was led to believe that i could use the excel formulas seeing that the design table that CATIA works from in this part comes from an Excel table, but this is no longer the case. – Castella Aug 20 '17 at 20:42

3 Answers3

0

Try:

Application.WorksheetFunction.YourFunction(YourArguments)

There's often a better solution than using worksheet functions in VBA, though.

Olly
  • 7,749
  • 1
  • 19
  • 38
  • Thank you for your help. apparently I'm going to have to use other means apart from worksheet functions... I have the following which gives me an answer based on the ColLine of the capscrew instead of the length, but I can't work it out. Any idea how to get this fixed? – Castella Aug 17 '17 at 13:38
  • 'declaration of X as "Height of Plate 9" Dim X As Integer X = length1.Value 'declaration of Y as the result for the optimal length of the CapScrew to be used Dim Y As Integer Y = 0 'Formula for the length of the CapScrew to be used If (designTable1.Configuration <= 15) = True Then Y = X - 10 - 1 + 15 designTable2.Configuration = Y Else Y = X - 12 - 1 + 15 designTable2.Configuration = Y End If – Castella Aug 17 '17 at 13:42
  • Don't post code in comments - edit your original question and add your amendments and code there. – Olly Aug 17 '17 at 14:07
  • Done Olly, apologies for that – Castella Aug 18 '17 at 15:03
  • Apologies if i'm mislead anyone with the issue at hand, i'll try to give you more detail: The code in the question only provides me with the line from the first column in the design table. Therefore, although the formula is correct the solution for Y is a much smaller capscrew than the desired. I need to have that formula use another column in the same design table and i do not know why, because by default it always selects the first one. – Castella Aug 20 '17 at 20:44
0

You can use a formula in CATIA to determine the configuration to use for the design table based on your inputs (Plate thickness and required excess).

CATIA provides the functions CloserConfig, CloserSupConfig, and CloserInfConfig for this purpose.

As an example suppose first consider this design table:

enter image description here

And there are three parameters: GRIP_LENGTH is a thickness that the screw is to hold together NUT_GRIP is the excess length required for a nut (or distance the screw is screwed into a plate). SCREW_LENGTH is the length of the chosen screw

The Formula valuates the configuration parameter of the design table choosing the closest available configuration where the screw length is greater then the total of GRIP_LENGTH and NUT_GRIP:

Configuration = Sheet->CloserSupConfig("SCREW_LENGTH",GRIP_LENGTH + NUT_GRIP)

enter image description here

From this formula the configuration is chosen automatically and the correct value for SCREW_LENGTH filled out from the design table.

C R Johnson
  • 964
  • 1
  • 5
  • 12
  • Thank you for your suggestion. Although not 100% correct I believe I am very close to the solution. An issue arises with the specific diameter of the screw. In the above formula the first correct screw length is selected. For example if I use a Support_Diameter of 50mm and a Height_of_Plate_9 of 17mm i get the selected screw:M3x25 and the correct selection should be M10x25. If, however I were to alter the Support_Diameter to anything above or equal to 53mm the selection should always start at M12. – Castella Aug 28 '17 at 10:01
0

Thanks to @Olly's suggestion I was able to find the right formula for my issue.

For the formula to work I created a column in the Capscrew design table that I called SuporteCheck. The Values of the column (which matched the rows of the desired screws M10 and M12) were the result of the formula: Height of Plate 9-Recess Length in Plate 9 - Height of Capscrew Head + Recess Length in Support. Then I used the above formula in the Capscrew configuration: CloserInfConfig("DesignTable.1","Altura_da_cabeça",External Parameters\Diametro_Parafuso ,"SuporteCheck",External Parameters\Espessura_Chapa_9 - 1mm -External Parameters\Diametro_Parafuso +External Parameters\entrada_seguranca

Thanks everyone.

Castella
  • 3
  • 1
  • 7