Consulting

Results 1 to 16 of 16

Thread: I need to make a program bullet proof

  1. #1
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location

    I need to make a program bullet proof

    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,
    Ron
    Windermere, FL

  2. #2
    VBAX Mentor tpoynton's Avatar
    Joined
    Feb 2005
    Location
    Clinton, MA
    Posts
    399
    Location
    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).

  3. #3
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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.

  4. #4
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    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,
    Ron
    Windermere, FL

  5. #5
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    You may also want to look at Conditional Compiling.

  6. #6
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    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,
    Ron
    Windermere, FL

  7. #7
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    Oh, boy!
    Excel 2008 does not support VBA, although 2004 and 2011 do.
    How do you feel about AppleScript?

  8. #8
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    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,
    Ron
    Windermere, FL

  9. #9
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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

  10. #10
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    Thanks, I saved a copy locally and started browsing through it. I can see that I have a lot of reading to do. ;-)
    Ron
    Windermere, FL

  11. #11
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    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,
    Ron
    Windermere, FL

  12. #12
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    With a Button, you'ld have to do something like
    [VBA]Sub ButtonClick()
    Call myOtherMacro(10)
    End Sub[/VBA]or[vba]Sub ButtonClick()
    Call myOtherMacro(Range("A1").Value)
    End Sub[/vba]or[vba]Sub ButtonClick()
    Call myOtherMacro(Application.InputBox("Enter the value",Type:=2))
    End Sub[/vba]

    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.

  13. #13
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    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,
    Ron
    Windermere, FL

  14. #14
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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.

  15. #15
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    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

    [vba]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[/vba]
    Ron
    Windermere, FL

  16. #16
    VBAX Expert
    Joined
    Aug 2007
    Location
    Windermere, FL, a 'burb in the greater Orlando metro area.
    Posts
    567
    Location
    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

    [vba]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[/vba]
    Ron
    Windermere, FL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •