Defining Constants

[johndmingione]

I'm having some trouble defining constants correctly.

I know the format should be this:

Const CONSTANT_NAME [As ConstantType] = value

But I'm a little unclear of how to word it. So here is the Line I need to change into a Constant. Normally I would have a BAS file do this but I can't find one (Refer to other post).

With oXLSheet.Range("A1:A10")
            .Font.ColorIndex = 50
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
        End With

That's the entire section that I'm dealing with. I don't think all of it needs to be constants, but maybe it does? I'm not sure. All I know is that the tutorial helping me with creating automation suggested that I turn my Code into Late-Bound to help with compatibility issues a User might have in regards to opening Excel. As a result, I have had to drop the Excel Reference that I was using therefore, having to define my own constants. As stated previous, here and in the other Thread, I wanted to make it easier by Inserting a BAS File into a Module as suggested and provided by the Tutorial I'm using but there were x64 bit compatibility issues with that so I couldn't. If anyone could help me turn the "xlCenter" part into a Constant it would be greatly appreciated. I am very new at this and still learning so I thank you for your patience.

Thank you for taking the time to read this

[DataMiser]

According to what I found online the value of xlCenter is -4108
http://fox.wikis.com/wc.dll?Wiki~ExcelConstants

Const xlCenter as integer =-4108

[jcis]

You shouldn't worry about Late Binding until your code is done, your code for Excel automation is already done? Have you been able to Format Excel rows according to Row Colors in your Flexgrid? (this is a reference to another thread)

It's true Late Binding will assure your Excel Automation App will work for any Excel version but the reasons for it to be the last thing you should do are.. for example: you lose intellisense, meaning you can't see Excel Objects properties from code, also you enter this nightmare about redeclaring your own constants to replace Excel contants ..etc.

But: In case you already have your code working, i'll give you some advice about declaring these constants: don't use another module (the one you were trying to download in the other thread), you don't need all Excel contants, you just need the contants you're using in your code, these can't be that many, maybe you're using 10, 15 or 20 different Excel constants, before removing Excel reference form your project just right-click each one of these constants in your code and select "Definition", this will open Object Browser and there you can see its value, then go and declare your own constant that will replace the Excel constant using this value, do the same for all. Use the syntax Datamiser told you here, if it will be declared in a module then declare it Public. Well, all this is explained in the Excel Automation Tutorial, but remember: all this (moving to Late Binding) should only be done when your code is done and working, don't ever change from early to late binding until development is done. If sometime in the future you need to add more code or making something new in this same Application then you'll add the Excel reference to your project again, you'll do all you need to do and then back to Late Binding and compile your project.

[dilettante]

You don't even need to play flippity-flop adding and deleting a reference to the Excel type library.

Simply change your object declarations to As Object for your production compile.

You can go ahead and use CreateObject instead of New when creating objects whether you strongly type them on not.

It is the strong typing (and early binding) that causes the compatability issue, not have a reference set.

[gibra]

You can use EARLY binding and LATE binding 'together' using the "Conditional Compilation Arguments" so no need to remove any reference from the project.
See the Project Properties window
Options-> Make tab
for Conditional Arguments setting

This applies to any application that supports Office Automation

See notes included on VB6 project attached.


Other examples of EARLY-LATE binding:
VB6
- Outlook: http://nuke.vbcorner.net/Articoli/VB...7/Default.aspx
VB.NET
- Excel: http://nuke.vbcorner.net/Progetti/NE...T/Default.aspx
- Word: http://nuke.vbcorner.net/Progetti/NE...T/Default.aspx

[jcis]

You don't even need to play flippity-flop adding and deleting a reference to the Excel type library.

Simply change your object declarations to As Object for your production compile.

You can go ahead and use CreateObject instead of New when creating objects whether you strongly type them on not.

It is the strong typing (and early binding) that causes the compatability issue, not have a reference set.

Exactly, I focused on Constants because he already has all the rest of the info he needs for changing to Late Binding in the tutorial he's following in the FAQ Forum (CreateObject / declare as Object..etc).

It's true you can keep the reference to Excel Library, it won't make any difference if it's there or not, i've always removed the reference from my project when the code was already using late binding and I was going to compile simply to be sure the code was 100% late binding, i mean for example maybe you're using Excel constant xlCenter, if the reference is there then VB will compile, but it shouldn't, this constant should be replaced, without the reference VB will fire an error telling you this constant has not been declared.

[johndmingione]

Thank you all for your responses. It helps me a lot, really. Unfortunately I didn't know that I shouldn't change to late-binding until the very end. I blindly followed the tutorial, that was my mistake.

My code isn't done. I'm playing around with the Automation in a separate form and the code I wrote essentially opens up a new form and not an existing one, which I need. So once I have this one up and functioning I'm going to switch the code to "Existing" and not "New." Unfortunately, I haven't even tested it in the FlexGrid itself.

I'm actually having a little trouble testing it inside the FlexGrid, because the code works as it should in my eyes. Since I wrote that paragraph above and now, I've switched the code to "Existing" from "New" but I can't seem to open it up inside the FlexGrid as needed. The User originally opens up the Excel Spreadsheet via an Open Button located in the File Tab in the top left. Should I add the Automation code to this? I remember you saying, JCIS, that I won't have to touch the original Code I would just have to add to it. Is this where I should add it?

[jcis]

It depends, the user opens excel then the flexgrid is loaded from Excel?

How are you going to resolve the "Row Matching" problem? (point 3 in the other thread)

[johndmingione]

No when the User clicks the EXE file he or she is presented with the Home Form, called RO Viewer. On the Home Form there is a Flex Grid that is blank. When the user clicks File, and then Open, it opens a Open File Window. When the User hits the Spreadsheet he or she wants to open and clicks "Open" then it populates the Flex Grid. I can paste the code if desired.

[jcis]

No when the User clicks the EXE file he or she is presented with the Home Form, called RO Viewer. On the Home Form there is a Flex Grid that is blank. When the user clicks File, and then Open, it opens a Open File Window. When the User hits the Spreadsheet he or she wants to open and clicks "Open" then it populates the Flex Grid. I can paste the code if desired.

Yes, that's what i meant, the user opens excel file (from inside RO Viewer) then the flexgrid is loaded, ok. What about the row matching problem? I remember the flexgrid is loaded using a query that uses DISTINCT and ORDER BY, this means for example that the Excel Worksheet could contain 20 rows, from these only 15 would be loaded into the Flexgrid, how are you going to match rows? maybe comparing all fields? what to do with those rows in Excel that have not been added into the Flexgrid?

[johndmingione]

I don't need anything to change within the Rows that are already there. So I guess wouldn't the safest thing to do be using the Exact rows that the Original Code uses? For example, in the DISTINCT and ORDER BY section you were talking about there are rows predefined. So why not use those same rows? The only thing I need to do is Save the Affected Colored Rows. Right now, all it does is change the color in the Flex Grid. I need to change it in Excel so it comes up colored the next time the User chooses that file.

[jcis]

Columns (Fields) are predefined, not rows. That query will bring most rows to Flexgrid but not all of them, in different order, the problem with this is that you won't be able to match rows by index (row number 1, row number 2,.. etc) because for example, row number 5 wasn't added to Flexgrid, or just because the order has been altered. One way to solve this would be comparing each row on Flexgird against all rows in Excel, if data in all columns for that row is the same then format that row in Excel, this would requiere a double loop, do you agree?

[johndmingione]

Oh!!! I'm sorry I read too fast and didn't understand what you were saying. My mistake, I apologize. Unfortunately you kind of lost me with the loop. It makes sense, comparing the rows in the FlexGrid to Excel. But I don't know how to create a loop. Perhaps you can help me? Or maybe there's a forum I should look at?

[jcis]

Check your PM's in Control Panel