8 Replies Latest reply on Feb 13, 2018 5:38 AM by Korbi Anis

    How to create a matrix from excel sheet using vba

    Korbi Anis

      Hi my friends , I have a excel file that contain two coloumn . The first one contain the X coordinates of points and the second one contain the Y coordinates.I want an VBA macro  to create a matrix  M1=[ n x1] wich contain all the X coordinates ( as described in the excel file) and onother matrix M2=[mx1] wich contains the Y coordinates.Thank you vey much for your support

        • Re: How to create a matrix from excel sheet using vba
          Attilio Colangelo

          Not sure what this has to do with the Solidworks API but here you go:

          Sub main()

          Dim mySheet As Worksheet

          Dim myWorkBook As Workbook

          Set myWorkBook = Excel.ActiveWorkbook

          Set mySheet = myWorkBook.ActiveSheet

          M1 = mySheet.Range("A1:A14")

          M2 = mySheet.Range("B1:B14")

          End Sub

            • Re: How to create a matrix from excel sheet using vba
              Korbi Anis

              Thank you very much dear friend for your help.It's too strange to get always 0 results when I excute a macro vba excel to call matlab function !

               

                Sub matlab()

               

                'Declaring the necessary variables.

                  Dim x()    As Variant

                  Dim y()    As Variant

                  Dim i       As Integer

                  Dim matlab  As Object

                  Dim Result  As String

                  Dim temp    As String

                  Dim R As Double

                  Dim res2 As String

                   Dim mySheet As Worksheet

               

               

              Dim myWorkBook As Workbook

               

               

                  'Get the input values.

               

              Set myWorkBook = Excel.ActiveWorkbook

               

               

              Set mySheet = myWorkBook.ActiveSheet

               

               

              x = mySheet.Range("A1:A14")

               

               

              y = mySheet.Range("B1:B14")

                 

                 

                

                  Set matlab = CreateObject("matlab.application")

                   fileToRun = "C:\Users\DOC\Desktop\Nouveau dossier\MinBoundSuite\incircle.m"

                  matlabCommand = "matlab -nodisplay -nosplash -nodesktop -r "" run('" & fileToRun & "');exit;"" "

                  call_r = Shell(matlabCommand)

                 

              'mFilePath = "C:\Users\DOC\Desktop\Nouveau dossier\MinBoundSuite\incircle.m"

                 ' matlab.Execute "cd(" & mFilePath & ")"

                  'Execute the built-in MATLAB funciton - trapz(X, Y), where X and Y are arrays with the same number of elements.

                  'The line below actually simulates the following code:

                  'Result = MATLAB.Execute("trapz([0,1,2,3,4,5,6,7,8,9],[0,4,16,36,64,100,144,196,256,324])")

                  Result = matlab.Execute("[C,R]=incircle(x,y)")

              res2 = matlab.Execute(" & R & ")

                 

                 

              Debug.Print " " & R

              End Sub

               

              someone can help me please ?

                • Re: How to create a matrix from excel sheet using vba
                  Kevin Chandler

                  Hello,

                   

                  Using the range statement to initialize the arrays isn't working, try the code below to load your arrays.

                  The number of data pairs, like in your code, is hard-coded, so you'll have to edit the vba code if this number changes (or you can also write some code to extract the number of entries in the "A" column).

                   

                  Cheers,

                   

                  Kevin

                   

                  '*********************************************************************************************

                  Sub matlab()

                   

                    'Declaring the necessary variables.

                   

                      Dim MaxDataElements As Long

                     

                      Dim x()    As Double

                      Dim y()    As Double

                     

                      Dim i       As Integer

                     

                      Dim rangex As String

                      Dim rangey As String

                      Dim CurrentX As Variant

                      Dim CurrentY As Variant

                      Dim xColumn As String

                      Dim yColumn As String

                     

                      MaxDataElements = 14

                      xColumn = "A"

                      yColumn = "B"

                     

                      ReDim x(MaxDataElements)

                      ReDim y(MaxDataElements)

                   

                      Dim matlab  As Object

                   

                      Dim Result  As String

                   

                      Dim temp    As String

                   

                      Dim R As Double

                   

                      Dim res2 As String

                   

                       Dim mySheet As Worksheet

                   

                   

                  Dim myWorkBook As Workbook

                     'Get the input values.

                  Set myWorkBook = Excel.ActiveWorkbook

                  Set mySheet = myWorkBook.ActiveSheet

                   

                  'MsgBox (MaxDataElements)

                   

                  For i = 1 To MaxDataElements

                      rangex = xColumn & i

                      rangey = yColumn & i

                      CurrentX = Range(rangex).Value

                      CurrentY = Range(rangey).Value

                      x(i) = CurrentX

                      y(i) = CurrentY

                      temp = "x(" & i & ")=" & x(i) & ", y(" & i & ")=" & y(i)

                      MsgBox (temp)

                  Next i

                   

                  End Sub

              • Re: How to create a matrix from excel sheet using vba
                Matthew Cempa

                Not sure if you need to use Matlab to find out if a point is inside a circle or if you need Matlab for other things as well.  I think a simple VBA solution would be quite easy to develop instead of invoking Matlab.  Plus it would be much quicker than launching Matlab.

                  • Re: How to create a matrix from excel sheet using vba
                    Korbi Anis

                    I appriciated your aswer dear Matthew.I tried to write a ba code wich allows to find the maximun inscribed circle of a set of points or a 2d sketch but I did not find the way.I found already a matlab script.m that give me the possiblity to determine the maximun inscribed circle called also ( incircle) of a set points.For this reason I tried to call the matlab function directly using vba.like this I can display the results of the function  ( center of the macimun inscribed circle and its radius) and exeplore them to drax the circle in solidworks.