tricks of gurus

[Greg Truby]

I can see the headlines now:

TRUBY GUIDES THREAD BACK ON TOPIC!!!! SHOCK KILLS HALF OF MREXCEL FORUM MEMBERS...
-R.Schollær

Aye, 'twas a bitter, bitter pill to swallow, laddie. And what with it bein' a lounge thread to boot, took me three tries to click that [SUBMIT] button.

...I must admit, I rarely use the properties to set CC, find it far easier (and more intuitive) using the constant, it is very easy to just set it up as a global constant (even though I abhor global variables in principle). You cane even set it as module scope variables, have different values in different modules, thereby focussing on particular code...
-xld

Shoot, I hadn't ever even paid any attention to the box for it in the project props dialog until I saw something in ****'s blog a while back. Felt like a real moron when I hopped into the VBE to verify that there really was such a box in that dialog..."hmmm, how long has that been under my nose?" I do put a comment at the top of any modules that reference the CC indicating that it's to be found in that dialog so that someone reading the code isn't left vainly looking for a #CONST statement.

 

[Oorang]

I have found conditional compiling great for code I plan on releasing using a late-binding approach. I prefer to use the intelisense when I am developing. So I make a #Const that determine early vs. late. Then set it to early and develop away. Then I just go back through my work, and add in the late bound approach. Which is pretty easy considering you just have to replace a few constants and change how you instantiate your object. And presto! To give an example of use, I was making extensive use of the Excel-Libraries from Access. Some users (me included) had Office 2003, which is Excel 11. Some however were on 2000. If you early-bind to Excel on 2003, when a user opens up your app in 2000, the app will break with a missing reference (it won't automatically find the 10.0 library.) To fix it, I needed to use a late bound approach, but I didn't feel like developing everything else without the intelisense. Here is an example for those who care

Option Explicit
#Const CCLateBind = True
Sub Example()
    #If CCLateBind Then
        Dim FS As Object
        Dim TS As Object
        Dim F As Object
        Set FS = CreateObject("Scripting.FileSystemObject")
        #Else
        'To use early binding, set a reference to "Microsoft Scripting Runtime", and
        'change the compiler-constant CCLateBind to "False".
        Dim FS As Scripting.FileSystemObject
        Dim TS As Scripting.TextStream
        Dim F As Scripting.File
        Set FS = New Scripting.FileSystemObject
        #End If
    MsgBox "See how this runs without freaking out... Yay!"
End Sub

 

[Greg Truby]

Here's a "trick of the gurus" that I've heard about, but not used myself and am curious about. Named ranges (or "named formulae" if you like) have a VISIBLE property. I.e. one can create hidden named items. In places where I've seen this mentioned, I get the impression that hidden named ranges/formulae come in handiest for constants of some type or in some way of tracking product registration? But I haven't really seen anything that was explicite about when they're particularly useful. Has anyone used hidden names? If so, where did the come in useful for you?

 

[erik.van.geit]

Here's a "trick of the gurus" that I've heard about, but not used myself and am curious about. Named ranges (or "named formulae" if you like) have a VISIBLE property. I.e. one can create hidden named items. In places where I've seen this mentioned, I get the impression that hidden named ranges/formulae come in handiest for constants of some type or in some way of tracking product registration? But I haven't really seen anything that was explicite about when they're particularly useful. Has anyone used hidden names? If so, where did the come in useful for you?

I used hidden names to store a passwordsystem on a hidden part of a sheet. The project had hundreds of users with mainly low Excelskills and was updated often. So it was quite safe.
If the names were not hidden, choosing for example the correct item from the namelist and pasting some password would have been too easy: now they needed a button.

I've seen projects with hidden formulas: the developers wanted to "protect" their formulas from being copied.

of course we know all those protecting systems are not very reliable any more-skilled user can easily undo protections of the kind

kind regards,
Erik

 

[Oorang]

Hiya Greg, thanks for the tip! I figured out how to add invisible names through VBA... It is possible to do so via GUI?

Hi Erik! Just as an FYI, I noticed invisible names still appear in the names collection. I am sure you understand the implication.

 

[xld]

Here's a "trick of the gurus" that I've heard about, but not used myself and am curious about. Named ranges (or "named formulae" if you like) have a VISIBLE property. I.e. one can create hidden named items. In places where I've seen this mentioned, I get the impression that hidden named ranges/formulae come in handiest for constants of some type or in some way of tracking product registration? But I haven't really seen anything that was explicite about when they're particularly useful. Has anyone used hidden names? If so, where did the come in useful for you?

I use hidden names extensively for any 'application' names, that I don't want users messing with. Using Jan Karel Pieterse's NameManager eases the maintenance.

 

[xld]

Which is pretty easy considering you just have to replace a few constants and change how you instantiate your object.

Why replace the constants, declare them in the CC

#Const EarlyBound = False

#If Not EarlyBound Then
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
#End If

Public Sub GetData()
#If EarlyBound Then
Dim oConn  As ADODB.Connection
Dim oRS As ADODB.Recordset
#Else
Dim oConn  As Object
Dim oRS As Object
#End If
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

    sFilename = "c:\Mytest\Volker1.xls"
    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & sFilename & ";" & _
                "Extended Properties=Excel 8.0;"

    sSQL = "SELECT * FROM [Sheet1$]"
    
#If EarlyBound Then
    Set oRS = New ADODB.Recordset
#Else
    Set oRS = CreateObject("ADODB.Recordset")
#End If
    
    sSQL = "SELECT * FROM BookLevelName"

    Set oRS = New ADODB.Recordset
    oRS.Open sSQL, sConnect, adOpenForwardOnly, _
                adLockReadOnly, adCmdText

    ' Check to make sure we received data.
    If Not oRS.EOF Then
        Sheet1.Range("A1").CopyFromRecordset oRS
    Else
        MsgBox "No records returned.", vbCritical
    End If

    ' Clean up our Recordset object.
    oRS.Close
    Set oRS = Nothing

End Sub

 

[Oorang]

I sometimes do that, It just depends on what I think is going to be quicker

 

[xld]

Hiya Greg, thanks for the tip! I figured out how to add invisible names through VBA... It is possible to do so via GUI?

As with very hidden sheets, not the Excel GUI.But JanKarel Pieterse's NameManager handles them okay.

 

[xld]

I sometimes do that, It just depends on what I think is going to be quicker

If you keep a list tucked away, this is always quicker.