I need to make a program bullet proof

[RonMcK]

Hi, All,

I have a program that I developed on my PC with assistance from MD Mackillop.

My current challenge is 'hardening' it so I can give it to a group of 10-12 coworkers, some of whom are not adept with Excel. And, they'll all be using MacBook Pros with XL2004 (we also have XL2008 but not 2011).

The Mac Pro desktop that I use is sequestered at work, so, this weekend I'm trying to remember XL2004's differences so I can finish the program, here at home, and just test it Tues when I return to work.

As I recall from work I did in 2007 with XL2004, the userforms I developed on the PC did not play on the Mac. Is this memory right or wrong? If it is, will VBA on XL2004 respond to command buttons on the XL sheet?

My ideas for hardening are to:
1. put the workbooks with the program, the source data, and the target worksheets (3 workbooks) in a folder,
2. have the user install this folder on his/her desktop (by unzipping it),
2. place a command button on the program worksheet to initiate the program,
3. have the program open the source and target files,
4. the program will walk the user through selecting questions for a test by repetitively presenting groups of questions from which the user selects one by clicking on any cell of the row containing the question.
5. Once all the questions needed for the test are selected the process ends.

If XL2004 will not respond to command buttons, what other choices do I have? Am I limited to locking the code and having the users use Tools > Macros > select the macro on the list and click Run?

Thanks,

[tpoynton]

I keep a copy of Excel 2003 installed to develop for 2004 on the mac. RE: the command buttons, if you created them as activex controls then they will not work on the mac. You can create buttons without using activex. I am on my mac now so I can not look at 2007, but I believe it is through the Forms control toolbox? You'll know you are using the right command button type if you can not change the back color (unless that capability has been added to 2007).

[mikerickson]

No that capability is not added. Forms controls are the way to go for cross-plaform.

Also, Excel 2004 does not support the VB function Split or Replace (and a few others). Split would require an emulator, Application.Worksheetfunction.Substitute works for Replace.

There are other differences, you'll need to do some testing.

Also, you should look at using Application.PathSeparator rather than a hard-coded "/"

Going forward, if some of your end users are on a Mac, its much easier to
write on the Mac and add cross platform considerations at the end
than it is to
write on a PC and add cross-platform considerations at the end.

[RonMcK]

Mik,

I picked up on your use of application.separator from one of the earlier threads. I wish I had known of that 4 years ago. I'm going to start using it so life is a bit simpler. I have a subroutine for determining the OS and setting a flag. I'll probably still need to use that even though I'll use the application.separator, my paths are enough different on the two systems.

I have a split routine to make up for the lack of a split command in XL2004 given to me by a former coworker.

Thanks for the advice to develop on the Mac. I think that I'll let this project sit until Tuesday when I'll have access to my Mac.

tpoynton, thanks for your advice and counsel, as well.

Thanks,

[mikerickson]

You may also want to look at Conditional Compiling.

[RonMcK]

Thanks. Conditional compiling is something else I noticed that you were using and recommending; I'm going to begin using it in my code that I run on both platforms.

I guess it's time for me to start asking about when we'll upgrade to XL2011 after all we just added XL2008 last fall.

Thanks,

[mikerickson]

Oh, boy!
Excel 2008 does not support VBA, although 2004 and 2011 do.
How do you feel about AppleScript?

[RonMcK]

Yea, O Boy! That lack of VBA in XL2008 is why we retained XL2004 on all of our Macs.

Applescript? Well, I'm happy to learn new things, I'm a recent convert to using Macs.

My first exposure was 6/12/2006 when I began working as a contract employee where I now work. PCs for the 20 years before that.

I'll have to get a book from the library on Applescript. Do you recommend any particular author(s)?

Thanks,

[mikerickson]

No. I'm learning/fiddling with it myself. Its awful slow, both the script and my learning of it.
But this from MicroSoft will help

http://download.microsoft.com/downlo...eScriptRef.pdf

[RonMcK]

Thanks, I saved a copy locally and started browsing through it. I can see that I have a lot of reading to do. ;-)

[RonMcK]

Mik, et al,

When I invoke a VBA program using a command button, can I feed it any variables to guide the program execution?

Or do I need to use a 'shell' program wherein I establish some global variables, set their values, and then pass them to the program I really want to be running?

Or, is there some other better way to do this?

Thanks,

[mikerickson]

With a Button, you'd have to do something like

Sub ButtonClick()
    Call myOtherMacro(10)
End Sub

or

Sub ButtonClick()
    Call myOtherMacro(Range("A1").Value)
End Sub

or

Sub ButtonClick()
    Call myOtherMacro(Application.InputBox("Enter the value",Type:=2))
End Sub

OR. If you set the otherMacro to have an optional argument, you could assign it to the Button by right clicking and Assign Macro. Even though the macro does not appear in the list in the dialog box, you can type the name of the Macro into the text box in the dialog and it will work.

Hmmm..
Given myOtherMacro that requries an argument.
One can call that macro and pass 12 as the argument to that sub by typing "myOtherMacro 12" (no quotes) into the text box in the Call Macro dialog. I should check to see if a similar technique could be used to assign it to a button with fixed arguments.

[RonMcK]

Thanks, Mik, I'll play with that a bit, tonight, and see what I can come up with.

The word at the office is that I'll have to make the business case for Office 2011 for Mac 'cause no else has asked for it, yet.

I asked a similar question in VBA Code & Other Help > Excel Help; it's http://www.vbaexpress.com/forum/showthread.php?t=37640 if you want to kill it and refer folks to this thread.


Thanks,

[mikerickson]

Actualy, the other thread will get better traffic, so I'll leave it alone.
I see from that thread that you are looking for user control of the variables passed to the routine, in which case a variation of #2 or #3 above would be the way I went.

[RonMcK]

Mik, et al,

I'm trying to read the directory that contains my source and target files, as well as my "program" XL file. I'm using the following development code which works beautifully on my PC and should work on the Mac from what I read in Help.

As noted below, the line "FileName = Dir(FileSpec)" fails to return anything and I've verified by the debug.print in the calling program that we are in the proper directory/folder. Does Mac expect something different form "*.xls" as the trailing piece of FileSpec ?

thanks,

Ron

Option Explicit

Sub Test_GetFileList()
    Dim p As String, x As Variant, i As Integer
    p = ThisWorkbook.Path & ":*.xls*"
    Debug.Print p
    x = GetFileList(p)
    Select Case IsArray(x)
        Case True 'files found
            MsgBox UBound(x)
            Sheets("Sheet1").Range("A:A").Clear
            For i = LBound(x) To UBound(x)
                Sheets("Sheet1").Cells(i, 1).Value = x(i)
            Next i
        Case False  'no files found
            MsgBox "No matching files"
    End Select
End Sub

Function GetFileList(FileSpec As String) As Variant
    '   Returns an array or filename that match FileSpec
    '   If no matching files are found, it returns False
    Dim FileArray() As Variant
    Dim FileCount As Integer
    Dim FileName As String
    FileCount = 0
    FileName = Dir(FileSpec)       '<<< this fails to return a file list
    Debug.Print Dir(FileSpec)
    If FileName = "" Then GoTo NoFilesFound
    '   Loop until no more matching files are found
    Do While FileName <> ""
        FileCount = FileCount + 1
        ReDim Preserve FileArray(1 To FileCount)
        FileArray(FileCount) = FileName
        FileName = Dir()
    Loop
    GetFileList = FileArray
    Exit Function
    NoFilesFound:
    GetFileList = False
End Function

[RonMcK]

Mik,

I've found some code by Ken Puls that has a version for Mac and it seems to be working; it's article 559 in the KB, as I recall. I'll need to adapt it to my program but using his function should solve my need to alert my user if either of the required files is missing.

Thanks,

Ron

Sub TestItWithMacintosh()
    ' Macro Purpose: To test the FileOrDirExists function with a Macintosh
    ' Only included to demonstrate the function.  NOT required for normal use!
    Dim Grade As String
    Grade = "G1"
    Dim sPath As String
    Dim path As String
    path = ThisWorkbook.path & Application.PathSeparator
    ' Change your directory here
    sPath = path & Grade & "_PascoCty.xls"
    ' Test if directory or file exists
    If FileOrDirExists(sPath) Then
        MsgBox sPath & " exists!"
    Else
        MsgBox sPath & " does not exist."
    End If
End Sub

Function FileOrDirExists(PathName As String) As Boolean
    ' Macro Purpose: Function returns TRUE if the specified file or folder exists, false if not.
    ' PathName     : Supports Windows mapped drives or UNC
    '             : Supports Macintosh paths
    ' File usage   : Provide full file path and extension
    ' Folder usage : Provide full folder path
    '               Accepts with/without trailing "\" (Windows)
    '               Accepts with/without trailing ":" (Macintosh)
    Dim iTemp As Integer
    ' Ignore errors to allow for error evaluation
    On Error Resume Next
    iTemp = GetAttr(PathName)
    ' Check if error exists and set response appropriately
    Select Case Err.Number
        Case Is = 0
            FileOrDirExists = True
        Case Else
            FileOrDirExists = False
    End Select
    ' Resume error checking
    On Error GoTo 0
End Function