Creating Cell Borders in Excel Using ActiveX

[diarmaede]

Hi All,

Thanks to everyone who has been helping me in my ActiveX adventures.

My current question is, How do I create LabVIEW code equivalent to these VB lines:

 Range("A1:B2").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

This is what I've got so far:

Any help would be greatly appreciated.

Sincerely,

FB

[Charles_CLA · Accepted solution]

You've gotten most of the way there. Keep in mind the Excel macro recorder is VERY inefficient so you can complete get rid of the select commands. Also, in Excel's VBE (visual basic editor) turn on the "Immediate Window" (Ctrl+G). In this window you can type something like this:

?xlNone

?xlEdgeLeft

to return the numeric value associated with the enum value. Additionally, pressing F2 in the VBE will bring up the object browser which will allow you to search for these object and find there numerical definition (immediate window tends to be faster 😉 )

Try the attached code and let us know if it works.

[diarmaede]

Charles, that code is a thing of beauty.  I'll modify it for my own nefarious porpoises and get back to you to let you know if everything worked out.

Cheers,

FB

[GregFreeman]

---

@Charles_CLD wrote:

?xlNone

?xlEdgeLeft

to return the numeric value associated with the enum value. Additionally, pressing F2 in the VBE will bring up the object browser which will allow you to search for these object and find there numerical definition (immediate window tends to be faster 😉 )

Try the attached code and let us know if it works.

---

Awesome, I never knew this trick and was always digging through MSDN (i.e. search google with MSDN in the search string )!

[diarmaede]

Hi Charles,

I'm still getting used to closing ActiveX references, and my understanding of this is really lacking.  With that in mind, could you please tell me why you did not need to close the "Range" reference in this bit of code:

Thanks!

FB

[GregFreeman]

---

@diarmaede wrote:

Hi Charles,

I'm still getting used to closing ActiveX references, and my understanding of this is really lacking.  With that in mind, could you please tell me why you did not need to close the "Range" reference in this bit of code:

Thanks!

FB

---

I think it was just a small mistake on his part. The references all should be closed, in the opposite order that they were open. So, the last to be opened should be the first to be closed. You don't want an issue where you close a reference that owns other open references, or from that point on you will be unable to close them and memory leaks will ensue. With specific LabVIEW references, I believe, it's a little bit different and once you use a reference to get new ones, you can close that reference and it actually helps performace. Again, this is just a statement I think I read somewhere, but I am not sure of all the cases where it holds true. If you look at the quick drop template in <LabVIEW>/resources/dialogs you can see an example of this where some references are closed right away, even though a property node was used to get other references which remain opened. I know it's a little off topic, but just to extend your knowledge with regards to refs in LabVIEW. Someone else may be able to comment on this little tidbit further.

[Charles_CLA]

---

I think it was just a small mistake on his part. The references all should be closed, in the opposite order that they were open. So, the last to be opened should be the first to be closed. You don't want an issue where you close a reference that owns other open references, or from that point on you will be unable to close them and memory leaks will ensue.

---

Yes, that was simply a mistake. All ActiveX references should ALWAYS be closed when you're done with them. 

[Charles_CLA]

FYI, don't close the reference until AFTER you have closed the "Borders" reference. In some cases, closing a parent's ActiveX reference will cause you to lose the child ActiveX reference as well. I don't *THINK* that would happen here but the best practice is to dispose of references in reverse order to how they were opened.

[diarmaede]

Ah!  Good!  I thought things were getting more complicated.  Thanks to both of you for the replies.

[diarmaede]

Hi Charles and All,
Your code worked beautifully!  Here is the end result in my program:

The "Borders" array is just a boolean array with each of the eight border positions.
The "False" case for each border position uses the code Charles wrote for the diagonal borders, which showed no lines in his original code.
The correct integer value for "xlThin" is "2."  Using "1" gives you a dotted line.

Using the program shown above, you can set whatever thin borders you want for whatever range of cells you want.  Adding code to vary the thickness and the other properties of the lines would be pretty easy, but I don't need to do that right now.

I'd post the code I wrote, but company policy forbids that.  Sorry.
Thanks again!
- FB