Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: back to 95 (95)

  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    back to 95 (95)

    I have a spreadsheet in Excel 97 (could be beyond 97) with a VBA macro function that I wrote. A friend has Excel 95. I am trying to provide him with a copy of my spreadsheet.

    When I work on my PC, I can save the spreadsheet as 95, no problem. However, when working on my machine, I see nothing different (compared to 97+) as far as the VBA environment.

    When I work on his machine, I see a Module sheet with my code. However, the cells that call the macro function have #NAME.

    So now the questions:
    1. is there something I can do on his machine to get Excel to recognize my code so that #NAME resolves to the macro function?
    2. Is there something I can do on my machine so that I can be working in an environment that looks like his? That is, if I write a macro, I'd like to put it on a Module sheet.

    That's about it for now. TIA

    Fred

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: back to 95 (95)

    What do you mean with <However, the cells that call the macro function have #NAME.>.
    Is your macro called by clicking a cell or so? In other words, is it based on events? Excel95 does not support events, as far as I know, only the auto_open and auto_close events.

    Or maybe, you just have to reassign the macros in the general modules to a button or however you access them. Anyhow, the macros in a general module (if not declared private) must be in the macro list and it should be possible to make them run from there. If so, it is a matter of reassigning the code.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: back to 95 (95)

    Hans,

    In this case, the macro is a UDF, say addx, in a regular module. So if I have a formula in C1
    =addx(a1,b1)
    I know how this works in 97+ with VBA.

    When my friend opened my spreadsheet in 95, he got a #NAME error. However, he did have a module sheet with my addx UDF (I guess that was part of the conversion - either when I saved it or when he opened it).

    How can I take this code and get it into a module sheet and have it recognized in 95?

    Similarly, how can I work on my 97 PC and see what he's seeing.

    For now, I have no need to generalize beyond a UDF. However, extensions to at least subs, including how to call them in a 95 environment, would be OK. I have no problems not dealing with event subs.

    Not sure what you meant by "macro list". Is this something from 95?

    Thks.

    Fred

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: back to 95 (95)

    Fred,

    I suggest you save your code as a text file (just copy the function code in notepad and save the text). The go to the computer of your friend where Excel95 is installed. Add a general module sheet in Excel 95 and copy the code from notepad to the general module. If the code starts with
    Function ...
    ...
    End Function

    then this function should be usable in the spreadsheets. The only possibility is that you use some Excel 97 specific code in that function, which is not recognized by Excel 95. Can you post your code?

    The list I was telling about is just the list of available macros that you see when you go to Tools >> Macro >> macros.

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: back to 95 (95)

    Hans,

    The code follows It's actually something that I posted about 2 months ago. Legare posted another version which I may need to go back to. I was using Data Validation to ensure that the values entered for the various inputs (eg, month, year, weekday, ordinal) were valid. However 95 does not have Validation and Legare's code did not assume the use of it. (how did he know back then that I wouldn't be able to use Data Validation? smart - very smart <img src=/S/clever.gif border=0 alt=clever width=15 height=15>)

    <pre>Option Explicit

    Public Function date_of_xth_day(s_month As String, s_year As String, _
    s_ordinal As Integer, s_day As String) As Variant
    '
    ' returns date corresponding to xth day (eg, 2nd tuesday) of a given month/year
    '
    'Dim s_month, s_year As String 'inputs for selected month and year
    'Dim s_ordinal As Integer 'input for ordinal day of month-eg 1st Tuesday
    'Dim s_day As String 'input for day of week
    Dim first_day_of_month As Integer 'calculated value (1-7) of first day of month
    Dim day_value As Integer 'calculated value (1-7) of desired day of week
    Dim day_offset As Integer 'calculated value of offset of desired day from first day
    Dim first_of_month As Date 'calculated date of first of requested month
    Dim first_of_next_month As Date 'calculated date of first of month after selected month
    Dim return_date As Date 'calculated date to return

    'Calculate needed dates, day numbers, etc
    first_of_month = CDate(s_month & " 1, " & s_year)
    first_of_next_month = DateAdd("m", 1, first_of_month) 'used to check if input error
    first_day_of_month = WeekDay(first_of_month, vbSunday)

    Select Case s_day 'get day number corresponding to selected day
    Case "Sunday"
    day_value = 1
    Case "Monday"
    day_value = 2
    Case "Tuesday"
    day_value = 3
    Case "Wednesday"
    day_value = 4
    Case "Thursday"
    day_value = 5
    Case "Friday"
    day_value = 6
    Case "Saturday"
    day_value = 7
    End Select

    day_offset = (day_value - first_day_of_month + 7) Mod 7
    'add 7 to paren to adjust for neg values since VBA Mod works differently than Excel Mod
    return_date = first_of_month + (7 * (s_ordinal - 1)) + day_offset

    If return_date >= first_of_next_month Then
    date_of_xth_day = "invalid input" 'could use CVErr(xlValue) for #VALUE error
    Else
    date_of_xth_day = return_date
    End If

    End Function

    </pre>



    So if I understood, in 95 there is no such thing as a PUBLIC function, is that correct? That may explain why his spreadsheet couldn't find the function. However, it seems I don't have to bring my code in a Notepad file to him since it was displayed (verbatim) in the Module sheet we saw on his PC. Didn't occur to me to delete "Public".

    The above is pretty straightforward except for a few built-in functions right after the Dim stmts. In Legare's version, he only used DateSerial to get the base (1st of desired month) and WeekDay (in the same way I used it). If necessary, I'll post his code but I don't think it should be.

    Thanks for the help.

    Fred

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: back to 95 (95)

    There are huge differences between VBA in 95 and 97, no module sheets but rather a separate development environment. You probably have to write it from scratch especially if you trigger off of any events tied to worksheets or buttons, etc.

    Deb

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: back to 95 (95)

    Deb,

    Thks. I realized this after playing around, somewhat futiley. But I'm just trying to do a UDF. So no worksheet events and no subs for buttons.

    I took 2 approaches to getting my 97 spreadsheet onto my friend's 95 excel environment. Both started with my 97 sheet saved as a 95 (only) workbook.
    1. when opening it in my friend's 95 excel, using the macro1 sheet that contained my UDF (somehow, this must be created when I do the save but I don't see this on my PC in 97)
    2. deleting my friend's macro1 sheet, inserting a blank module sheet, and pasting my code (which had been preserved as a text file, per Hans's suggestion) into the module sheet.

    I got mixed results. But in general, there were 2 problems that kept coming up:
    1. when calling my UDF, I would randomly get a Type Mismatch error. I wasn't sure if this was due to the fact that I was passing numeric cells into the UDF with arguments declared as integer. I tried changing the UDF numeric arguments to long with no better results
    2. altho both approaches above seemed to work initially, the worksheet became unstable such that the cells that called my UDF showed a #NAME error. I could close and re-open the sheet w/o saving changes and that would seem to solve things for a short time before #NAME would pop up again. The UDF is used in the Input sheet, in cells F5 down the column (see attached).

    Ideas?

    Thks.

    Fred
    Attached Files Attached Files

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: back to 95 (95)

    What happens if you:

    - Declare all variables in the argument list as variant
    - comment out all msgboxes

    Have you tried debug, compile in XL95? (fail to recall if it has that option)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: back to 95 (95)

    Jan Karel,

    One of my concerns is trouble-shooting this at my friend's house. I don't really want to do that.

    There appears to be no VBA environment in 95 as there is in 97. My code, when I open it in my friend's 95 Excel, appears on a sheet called Macro1. Even following Hans's suggestion from earlier in the thread, I inserted a Module sheet and pasted my 97 VBA code into it (having saved it in a text file first). So it would not seem that there is a debug/compile option in 95 altho I wouldn't swear to it.

    While I could try declaring the argument variables as variant and commenting out the msgboxes, I'm not sure I see how this would help - especially on the issue of the linkage between the worksheet and the module sheet seemingly becoming broken. I can give this a try the next time I'm there. But it would be more useful if I could get something going on my machine.

    Is there anything I could do to see what's happening in a 95 environment but on my machine?

    Thks.

    Fred

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: back to 95 (95)

    I think that the only thing that you could do would be to install 95. That will most likely screw up your 97.

    Why don't you convince your friend to upgrade to 97?
    Legare Coleman

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: back to 95 (95)

    I suggested to comment out the msgboxes because some things are not allowed in UDF's, which might have changed going from xl95 to 97 and up. That might explain the #Name! error you got in 95.

    <<Is there anything I could do to see what's happening in a 95 environment but on my machine?>>

    Sure, install XL95 <g>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: back to 95 (95)

    '95 should install nicely beside '97, I had no trouble installing xl5 after xl2000.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: back to 95 (95)

    Jan Karel,

    Thks. I could give the msgbox-deletion approach a try. It's worth it. I don't understand, tho, why the UDF would function for a little bit of time and then become unrecognized. Is it that when initially loaded, the UDF hasn't executed the code yet but the first time thru it becomes unrecognized bcs of passing thru the code with the msgbox (even tho they're not executed)? I didn't really observe closely when the #NAME occurred but it didn't take very long. Maybe the 1st time thru.

    However, the msgbox provides a msg for an erroneous input. Hmmm, no data validation (how I avoided erroneous inputs in 97) and no msgbox. How pre-historic do I get? I think me going back to 95 is out of the question.

    I had originally done the routine as worksheet formulas before going to a UDF. The only thing this didn't do nicely was to catch a 5-th xday (say Friday) which didn't occur in the current month. (It also needed a few intermediate variables). Maybe I should go back to that approach and use some hidden cols.

    Thks for the help.

    Fred

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: back to 95 (95)

    I installed 95 after 97 (quite a while back) and did have problems. The one that sticks in my mind was that I could no longer double click on a workbook with a long filename that included bllanks in the name. I think that there were also some other issues that I had to fix.
    Legare Coleman

  15. #15
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: back to 95 (95)

    Legare,

    I don't know if you looked at the code but if you did, this is your code from about 1-2 months ago when I started working this problem of nth_day_of_month. I think when I first posted this, I had converted to a subroutine, maybe it was my first UDF. You came back with the UDF that I've pretty much incorporated bcs of the error checking. My sub/UDF assumed use of data validation to avoid erroneous inputs; your UDF checked all the inputs (almost; I've added a few). Anyway, how omniscient of you. At that time, I didn't know my friend was running on 95.

    I had originally done the sub/UDF as worksheet formulas before going to a UDF. The only thing this didn't do nicely was to catch a 5-th xday (say Friday) which didn't occur in the current month. (It also needed a few intermediate variables). Maybe I should go back to that approach and use some hidden cols.

    Thks.

    Fred

Page 1 of 2 12 LastLast

Posting Permissions

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