writing edges and vertices with an Excel Macro

Sep 6, 2011 at 2:49 AM

I seem to be able to read from these worksheets with a macro, but when I try to use a macro to write into the edges and vertices columns the macro hangs.... are these columns protected in some way...I know I can directly type into them

Sep 6, 2011 at 4:59 PM
Edited Sep 6, 2011 at 5:02 PM

No, they are not protected.  Below my signature you'll find a macro I inserted in a NodeXL workbook to demonstrate that you can indeed fill in the Edges table using VBA.  It goes in the "ThisWorkbook" object in the VBAProject that Excel creates in the Microsoft Visual Basic window.  Try this, see if it works for you (it should), and then look at what's different in your own macro.

If you can't figure out what's wrong, post a simple macro that doesn't work and I'll take a look at it.  (Please don't post a huge program.)

-- Tony

 

Sub FillInEdgesTable()

    Dim edgeWorksheet As Worksheet
    Set edgeWorksheet = Me.Worksheets("Edges")
   
    Dim edgeTable As ListObject
    Set edgeTable = edgeWorksheet.ListObjects("Edges")
   
    Dim edgeTableRows As ListRows
    Set edgeTableRows = edgeTable.ListRows
   
    Dim newRow As ListRow
    Set newRow = edgeTableRows.Add
     
    Dim newRowRange As Range
    Set newRowRange = newRow.Range
   
    newRowRange.Cells(1, 1).Value = "First Vertex"
    newRowRange.Cells(1, 2).Value = "Second Vertex"
   
End Sub

Sep 7, 2011 at 7:22 AM
funny....I did miss putting a '.value' on the LHS variable though usually it works anyway...it works now but not sure if there were other reasons as it wrote 1 cell before it hung before....I tried yours and it didn't write anything...strange....

Dr Laurence Lock Lee
Ph: 0407001628



On 07/09/2011, at 1:59 AM, tcap479 wrote:

From: tcap479

No, they are not protected. Below my signature you'll find a macro I inserted in a NodeXL workbook to demonstrate that you can indeed fill in the Edges worksheet with VBA. It goes in the "ThisWorkbook" object in the VBAProject that Excel creates in the Microsoft Visual Basic window. Try this, see if it works for you (it should), and then look at what's different in your own macro.

If you can't figure out what's wrong, post a simple macro that doesn't work for you and I'll take a look at it. (Don't post a huge program.)

-- Tony

Sub FillInEdgesTable()

Dim edgeWorksheet As Worksheet
Set edgeWorksheet = Me.Worksheets("Edges")

Dim edgeTable As ListObject
Set edgeTable = edgeWorksheet.ListObjects("Edges")

Dim edgeTableRows As ListRows
Set edgeTableRows = edgeTable.ListRows

Dim newRow As ListRow
Set newRow = edgeTableRows.Add

Dim newRowRange As Range
Set newRowRange = newRow.Range

newRowRange.Cells(1, 1).Value = "First Vertex"
newRowRange.Cells(1, 2).Value = "Second Vertex"

End Sub


Sep 7, 2011 at 6:15 PM
Edited Sep 7, 2011 at 6:31 PM

"Value" is the Range class's default property, so you can omit it and it will work just fine.  The following two lines are equivalent:

newRowRange.Cells(1, 1).Value = "First Vertex"

newRowRange.Cells(1, 1) = "First Vertex"

I don't know of any reason why my code wouldn't work on your computer, or at least raise an error.

-- Tony