Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Modules - referring to workbook properties
I need some assistance from someone who is knows about Class Modules please.
I'm trying to set up some Class Mods so that I can refer to a Workbook/Sheet/Range using a variety of the properties. So far I have the following in a class module called CTLS: Option Explicit Private pTLS As Workbook Private Sub Init() Set pTLS = Workbooks("1. TOOLS.xls") End Sub Public Property Get TLS() As Workbook Attribute TT.VB_UserMemId = 0 'THIS LINE IS NOT VISIBLE in VBE If pTLS Is Nothing Then Init Set TLS = pTLS End Property Public Property Get Sheets() As Excel.Sheets Set Sheets = pTLS.Sheets End Property Public Property Get Name() As String Name = pTLS.Name End Property Private Sub Class_Initialize() Init End Sub Private Sub Class_Terminate() Set pTLS = Nothing End Sub and in a normal module I have Option Explicit Public TLS As New CTLS Public TT As New CTT The next Class Module is CTT: Option Explicit Private pTT As Worksheet Private Sub Init() Set pTT = Workbooks("1. TOOLS.xls").Worksheets("TOOLS") End Sub Public Property Get TT() As Worksheet If pTT Is Nothing Then Init Set TT = pTT End Property Private Sub Class_Initialize() Init End Sub Private Sub Class_Terminate() Set pTT = Nothing End Sub So, with this I can type into the Immed Window ? TLS.Name 1. TOOLS.xls ? TLS.sheets("TOOLS").name TOOLS and get the correct results as shown there. What I can't figure out (and I've read Chip's site and read Walkenbach's chapters) is how to do some other things such as TLS.save TLS.activate TT.activate (TT is a sheet) TT.select TT.visible = false etc etc In particular I need to be able to refer to ANY range in TT, which I imagine will need another Class Mod called (say) CTrng (C for class, T for TOOLS). I need to be able to do any of the usual things that we do with ranges. For the record, the Attribute statement was added via Notepad, and then the class module imported from the notepad, so that auto-instancing is set up. Therefore the values of TLS and TT are always refreshed when they are needed. From this point I am completely stuck. Class Modules are not the easiest things to wrap my head around, and any assistance will be greatly appreciated. Regards, Brett. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Modules - referring to workbook properties
I am not absolutely clear what you are trying to do, but I would guess that
you are trying to have a class for each workbook that you open? If so, I would create a workbook class, and open workbooks from that class, and add each instance of that class to a workbooks collection class. Then I would include a save and activate method. But I have to ask, why bother? Excel does it pretty well for you. -- __________________________________ HTH Bob "Brett" wrote in message ... I need some assistance from someone who is knows about Class Modules please. I'm trying to set up some Class Mods so that I can refer to a Workbook/Sheet/Range using a variety of the properties. So far I have the following in a class module called CTLS: Option Explicit Private pTLS As Workbook Private Sub Init() Set pTLS = Workbooks("1. TOOLS.xls") End Sub Public Property Get TLS() As Workbook Attribute TT.VB_UserMemId = 0 'THIS LINE IS NOT VISIBLE in VBE If pTLS Is Nothing Then Init Set TLS = pTLS End Property Public Property Get Sheets() As Excel.Sheets Set Sheets = pTLS.Sheets End Property Public Property Get Name() As String Name = pTLS.Name End Property Private Sub Class_Initialize() Init End Sub Private Sub Class_Terminate() Set pTLS = Nothing End Sub and in a normal module I have Option Explicit Public TLS As New CTLS Public TT As New CTT The next Class Module is CTT: Option Explicit Private pTT As Worksheet Private Sub Init() Set pTT = Workbooks("1. TOOLS.xls").Worksheets("TOOLS") End Sub Public Property Get TT() As Worksheet If pTT Is Nothing Then Init Set TT = pTT End Property Private Sub Class_Initialize() Init End Sub Private Sub Class_Terminate() Set pTT = Nothing End Sub So, with this I can type into the Immed Window ? TLS.Name 1. TOOLS.xls ? TLS.sheets("TOOLS").name TOOLS and get the correct results as shown there. What I can't figure out (and I've read Chip's site and read Walkenbach's chapters) is how to do some other things such as TLS.save TLS.activate TT.activate (TT is a sheet) TT.select TT.visible = false etc etc In particular I need to be able to refer to ANY range in TT, which I imagine will need another Class Mod called (say) CTrng (C for class, T for TOOLS). I need to be able to do any of the usual things that we do with ranges. For the record, the Attribute statement was added via Notepad, and then the class module imported from the notepad, so that auto-instancing is set up. Therefore the values of TLS and TT are always refreshed when they are needed. From this point I am completely stuck. Class Modules are not the easiest things to wrap my head around, and any assistance will be greatly appreciated. Regards, Brett. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Modules - referring to workbook properties
Hi Bob, thanks for replying. I'm beginning to think that you're probably
right - "why bother". Perhaps if I explain how I got into this. The following is a posting I did two days ago, and Chip's solution seemed to be intriguing, but I'm thinking that I didn't explain myself well enough, and perhaps inadvertantly mislead him (and others). I have come to this conclusion because I have to create several classes, with several Get/Let properties in each, and that seems to be self-defeating. The other thing too of course is that as I've never worked with classes before it seemed a good opportunity to learn about them (because I'm a sponge for knowledge). THE POSTING (under the title of "Public Variable dilemma" on April 23rd): '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''' I have two problems: 1. I have a workbook called "1. TOOLS.xls" that is almost always open (I can't do much without it). I want to set up VB with either a Public variable or a Public Const so that I can just refer to this book as TLS and to it's two main worksheets by T_TL and T_CONT. I know that I can use Public TLS as Workbook and then set it when I open 1. TOOLS with Set TLS = Workbooks("1. TOOLS.xls") and then set the sheets, but the problem is that if I have a debug instance and have to reset then I lose the value of the Public variables. I don't think there is a way of getting them back without manually resetting them again, which just won't do. Even if I had Public Const TLS as String = "1. TOOLS.xls" I still have the problem os setting it, and as far as I can see I can't have Public Const TLS as Workbook = Workbooks("1. TOOLS.xls") SO THE QUESTION IS HOW DO I DO IT PLEASE? The second problem follows on from that. I have a workbook "1. FINANCE surname.xls" where surname varies. This book is the centre of the system that runs of it (about ten workbooks). I want to be able to do the same thing with this workbook as described above for TOOLS, the difference being that FINANCE has the variable surname in it. I currently have a macro the does all the setting for the sheets when ant FINANCE is opened, but again if there's a crash then the Public variables lose their values. If I have to set them every time I run one of scores of macros then it defeats the purpose of having a Public variable (which is supposed to making life easier!). Thanks for any help that can be supplied. Regards, Brett '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''' Now I'm thinking that there must be an easier way to achieve this. Previously, I had been declaring Dim xxx as workbook, yyy as worksheet them setting them each time, but I want to cut down the code because they are used so many times, and a set of Public variables seemed the way to go. The problem with that is that the variables are cleared if the code crashes (I know it's not supposed to but I'm still developing it) so I'm looking for a way of keeping the variables intact, but also being able to use any property I need to, at any given time. Hopefully that gives you a better understanding than I gave to Chip. Regards, Brett |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Modules - referring to workbook properties
Hi Brett,
The reason I said why bother is because my understanding is just that you are trying to emulate what is already built-in, that is building a collection of workbook objects. Excel already maintains that for you. Also, if you have a debug instance that loses your public variable, it is just as likely to lose you class instances, so classes won't resolve your problem (trust me, I am a professional developer, and I always add debug code to my projects to re-initialise my classes when I get a debug that destroys them <g). What you can do is something along these lines If TLS Is Nothing Then Set TLS = Workbooks("1. TOOLS.xls") End If If you add this to all of your procedures that will use TLS, you should be able to ensure it is maintained, even after debug. If you want an example of classes, specifically collection classes (which are the most useful IMO), here is a simple example you can play with. Get back to me if you have any questions. Class Person Option Explicit Private mmName As String Private mmDOB As Date Public Property Let Name(ByVal Name As String) mmName = Name End Property Public Property Get Name() As String Name = mmName End Property Public Property Let DateOfBirth(ByVal DoB As Date) mmDOB = DoB End Property Public Property Get DateOfBirth() As Date DateOfBirth = mmDOB End Property Public Property Get LongDoB() As String LongDoB = Format(mmDOB, "d mmmm yyyy") End Property Public Property Get DayOfBirth() As String DayOfBirth = Format(mmDOB, "dddd") End Property Class Family Option Explicit Private mmPeople As Collection Function NewEnum() As IUnknown Set NewEnum = mmPeople.[_NewEnum] End Function Public Function Add(Being As Person) mmPeople.Add Being, Being.Name End Function Public Property Get Count() As Long Count = mmPeople.Count End Property Public Property Get Items() As Collection Set Items = mmPeople End Property Public Property Get Item(Index As Variant) As Person Set Item = mmPeople(Index) End Property Public Sub Remove(Index As Variant) mmPeople.Remove Index End Sub Private Sub Class_Initialize() Set mmPeople = New Collection End Sub Private Sub Class_Terminate() Set mmPeople = Nothing End Sub Standard Module Option Explicit Public Sub CreateAFamily() Dim mpFamily As Family Dim mpPerson As Person Set mpFamily = New Family Set mpPerson = New Person With mpPerson .Name = "Bob" .DateOfBirth = #9/16/1949# mpFamily.Add mpPerson End With Set mpPerson = Nothing Set mpPerson = New Person With mpPerson .Name = "Lynne" .DateOfBirth = #4/5/1956# mpFamily.Add mpPerson End With Set mpPerson = Nothing For Each mpPerson In mpFamily Debug.Print mpPerson.Name & " was born on " & mpPerson.LongDoB & ", and is a " & mpPerson.DayOfBirth & "'s child" Next mpPerson Set mpFamily = Nothing End Sub -- __________________________________ HTH Bob "Brett" wrote in message ... Hi Bob, thanks for replying. I'm beginning to think that you're probably right - "why bother". Perhaps if I explain how I got into this. The following is a posting I did two days ago, and Chip's solution seemed to be intriguing, but I'm thinking that I didn't explain myself well enough, and perhaps inadvertantly mislead him (and others). I have come to this conclusion because I have to create several classes, with several Get/Let properties in each, and that seems to be self-defeating. The other thing too of course is that as I've never worked with classes before it seemed a good opportunity to learn about them (because I'm a sponge for knowledge). THE POSTING (under the title of "Public Variable dilemma" on April 23rd): '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''' I have two problems: 1. I have a workbook called "1. TOOLS.xls" that is almost always open (I can't do much without it). I want to set up VB with either a Public variable or a Public Const so that I can just refer to this book as TLS and to it's two main worksheets by T_TL and T_CONT. I know that I can use Public TLS as Workbook and then set it when I open 1. TOOLS with Set TLS = Workbooks("1. TOOLS.xls") and then set the sheets, but the problem is that if I have a debug instance and have to reset then I lose the value of the Public variables. I don't think there is a way of getting them back without manually resetting them again, which just won't do. Even if I had Public Const TLS as String = "1. TOOLS.xls" I still have the problem os setting it, and as far as I can see I can't have Public Const TLS as Workbook = Workbooks("1. TOOLS.xls") SO THE QUESTION IS HOW DO I DO IT PLEASE? The second problem follows on from that. I have a workbook "1. FINANCE surname.xls" where surname varies. This book is the centre of the system that runs of it (about ten workbooks). I want to be able to do the same thing with this workbook as described above for TOOLS, the difference being that FINANCE has the variable surname in it. I currently have a macro the does all the setting for the sheets when ant FINANCE is opened, but again if there's a crash then the Public variables lose their values. If I have to set them every time I run one of scores of macros then it defeats the purpose of having a Public variable (which is supposed to making life easier!). Thanks for any help that can be supplied. Regards, Brett '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''' Now I'm thinking that there must be an easier way to achieve this. Previously, I had been declaring Dim xxx as workbook, yyy as worksheet them setting them each time, but I want to cut down the code because they are used so many times, and a set of Public variables seemed the way to go. The problem with that is that the variables are cleared if the code crashes (I know it's not supposed to but I'm still developing it) so I'm looking for a way of keeping the variables intact, but also being able to use any property I need to, at any given time. Hopefully that gives you a better understanding than I gave to Chip. Regards, Brett |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Modules - referring to workbook properties
Thanks Bob, only had a quick read because a Thai curry is waiting for me.
Actually, I'm waiting for it. Your suggestion looks like the go, and probably very similar to what Dave Peterson suggested two days ago. Hmmm, maybe I shouldn't have abandoned that idea ( a bit like the Looney Tunes "last of the Mohicans" (?) who smells Mohican burning "Wait a minute, me last of Mohicans, must be me YEEEOOOOW". Absolute crack up. BTW, does "Class Person" refer to you or me? Lol. I was looking through some old Class posts yesterday and you were once again referring to Class Person, with Bob's birthday of somewhere in 1949 (as for my sister) and "Lynne's" birthday was 1956 (same as me). Bit of a giveaway isn't it? Regards, Brett. "Bob Phillips" wrote: Hi Brett, The reason I said why bother is because my understanding is just that you are trying to emulate what is already built-in, that is building a collection of workbook objects. Excel already maintains that for you. Also, if you have a debug instance that loses your public variable, it is just as likely to lose you class instances, so classes won't resolve your problem (trust me, I am a professional developer, and I always add debug code to my projects to re-initialise my classes when I get a debug that destroys them <g). What you can do is something along these lines If TLS Is Nothing Then Set TLS = Workbooks("1. TOOLS.xls") End If If you add this to all of your procedures that will use TLS, you should be able to ensure it is maintained, even after debug. If you want an example of classes, specifically collection classes (which are the most useful IMO), here is a simple example you can play with. Get back to me if you have any questions. Class Person Option Explicit Private mmName As String Private mmDOB As Date Public Property Let Name(ByVal Name As String) mmName = Name End Property Public Property Get Name() As String Name = mmName End Property Public Property Let DateOfBirth(ByVal DoB As Date) mmDOB = DoB End Property Public Property Get DateOfBirth() As Date DateOfBirth = mmDOB End Property Public Property Get LongDoB() As String LongDoB = Format(mmDOB, "d mmmm yyyy") End Property Public Property Get DayOfBirth() As String DayOfBirth = Format(mmDOB, "dddd") End Property Class Family Option Explicit Private mmPeople As Collection Function NewEnum() As IUnknown Set NewEnum = mmPeople.[_NewEnum] End Function Public Function Add(Being As Person) mmPeople.Add Being, Being.Name End Function Public Property Get Count() As Long Count = mmPeople.Count End Property Public Property Get Items() As Collection Set Items = mmPeople End Property Public Property Get Item(Index As Variant) As Person Set Item = mmPeople(Index) End Property Public Sub Remove(Index As Variant) mmPeople.Remove Index End Sub Private Sub Class_Initialize() Set mmPeople = New Collection End Sub Private Sub Class_Terminate() Set mmPeople = Nothing End Sub Standard Module Option Explicit Public Sub CreateAFamily() Dim mpFamily As Family Dim mpPerson As Person Set mpFamily = New Family Set mpPerson = New Person With mpPerson .Name = "Bob" .DateOfBirth = #9/16/1949# mpFamily.Add mpPerson End With Set mpPerson = Nothing Set mpPerson = New Person With mpPerson .Name = "Lynne" .DateOfBirth = #4/5/1956# mpFamily.Add mpPerson End With Set mpPerson = Nothing For Each mpPerson In mpFamily Debug.Print mpPerson.Name & " was born on " & mpPerson.LongDoB & ", and is a " & mpPerson.DayOfBirth & "'s child" Next mpPerson Set mpFamily = Nothing End Sub -- __________________________________ HTH Bob "Brett" wrote in message ... Hi Bob, thanks for replying. I'm beginning to think that you're probably right - "why bother". Perhaps if I explain how I got into this. The following is a posting I did two days ago, and Chip's solution seemed to be intriguing, but I'm thinking that I didn't explain myself well enough, and perhaps inadvertantly mislead him (and others). I have come to this conclusion because I have to create several classes, with several Get/Let properties in each, and that seems to be self-defeating. The other thing too of course is that as I've never worked with classes before it seemed a good opportunity to learn about them (because I'm a sponge for knowledge). THE POSTING (under the title of "Public Variable dilemma" on April 23rd): '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''' I have two problems: 1. I have a workbook called "1. TOOLS.xls" that is almost always open (I can't do much without it). I want to set up VB with either a Public variable or a Public Const so that I can just refer to this book as TLS and to it's two main worksheets by T_TL and T_CONT. I know that I can use Public TLS as Workbook and then set it when I open 1. TOOLS with Set TLS = Workbooks("1. TOOLS.xls") and then set the sheets, but the problem is that if I have a debug instance and have to reset then I lose the value of the Public variables. I don't think there is a way of getting them back without manually resetting them again, which just won't do. Even if I had Public Const TLS as String = "1. TOOLS.xls" I still have the problem os setting it, and as far as I can see I can't have Public Const TLS as Workbook = Workbooks("1. TOOLS.xls") SO THE QUESTION IS HOW DO I DO IT PLEASE? The second problem follows on from that. I have a workbook "1. FINANCE surname.xls" where surname varies. This book is the centre of the system that runs of it (about ten workbooks). I want to be able to do the same thing with this workbook as described above for TOOLS, the difference being that FINANCE has the variable surname in it. I currently have a macro the does all the setting for the sheets when ant FINANCE is opened, but again if there's a crash then the Public variables lose their values. If I have to set them every time I run one of scores of macros then it defeats the purpose of having a Public variable (which is supposed to making life easier!). Thanks for any help that can be supplied. Regards, Brett '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''' Now I'm thinking that there must be an easier way to achieve this. Previously, I had been declaring Dim xxx as workbook, yyy as worksheet them setting them each time, but I want to cut down the code because they are used so many times, and a set of Public variables seemed the way to go. The problem with that is that the variables are cleared if the code crashes (I know it's not supposed to but I'm still developing it) so I'm looking for a way of keeping the variables intact, but also being able to use any property I need to, at any given time. Hopefully that gives you a better understanding than I gave to Chip. Regards, Brett |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Modules - referring to workbook properties
"Brett" wrote in message ... Thanks Bob, only had a quick read because a Thai curry is waiting for me. Actually, I'm waiting for it. Bit early for a curry isn't it, it is only midday here? BTW, does "Class Person" refer to you or me? Lol. I was assuming we both were. I was looking through some old Class posts yesterday and you were once again referring to Class Person, with Bob's birthday of somewhere in 1949 (as for my sister) and "Lynne's" birthday was 1956 (same as me). Bit of a giveaway isn't it? Haven't you heard of class obfuscation? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Modules - referring to workbook properties
ha! Yeah, and I, of course, was the class clown. There was a funny post I saw
in my search headed up "Class struggle". Very appropriate. Never too early for curry mate. It's now 11.35 here in Sydney. Curry was goot too. Cheers. "Brett" wrote: I need some assistance from someone who is knows about Class Modules please. I'm trying to set up some Class Mods so that I can refer to a Workbook/Sheet/Range using a variety of the properties. So far I have the following in a class module called CTLS: Option Explicit Private pTLS As Workbook Private Sub Init() Set pTLS = Workbooks("1. TOOLS.xls") End Sub Public Property Get TLS() As Workbook Attribute TT.VB_UserMemId = 0 'THIS LINE IS NOT VISIBLE in VBE If pTLS Is Nothing Then Init Set TLS = pTLS End Property Public Property Get Sheets() As Excel.Sheets Set Sheets = pTLS.Sheets End Property Public Property Get Name() As String Name = pTLS.Name End Property Private Sub Class_Initialize() Init End Sub Private Sub Class_Terminate() Set pTLS = Nothing End Sub and in a normal module I have Option Explicit Public TLS As New CTLS Public TT As New CTT The next Class Module is CTT: Option Explicit Private pTT As Worksheet Private Sub Init() Set pTT = Workbooks("1. TOOLS.xls").Worksheets("TOOLS") End Sub Public Property Get TT() As Worksheet If pTT Is Nothing Then Init Set TT = pTT End Property Private Sub Class_Initialize() Init End Sub Private Sub Class_Terminate() Set pTT = Nothing End Sub So, with this I can type into the Immed Window ? TLS.Name 1. TOOLS.xls ? TLS.sheets("TOOLS").name TOOLS and get the correct results as shown there. What I can't figure out (and I've read Chip's site and read Walkenbach's chapters) is how to do some other things such as TLS.save TLS.activate TT.activate (TT is a sheet) TT.select TT.visible = false etc etc In particular I need to be able to refer to ANY range in TT, which I imagine will need another Class Mod called (say) CTrng (C for class, T for TOOLS). I need to be able to do any of the usual things that we do with ranges. For the record, the Attribute statement was added via Notepad, and then the class module imported from the notepad, so that auto-instancing is set up. Therefore the values of TLS and TT are always refreshed when they are needed. From this point I am completely stuck. Class Modules are not the easiest things to wrap my head around, and any assistance will be greatly appreciated. Regards, Brett. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Modules - referring to workbook properties
Hi Bob
I posted a question yesterday but haven't had any takers, and now it's becomng urgent (it's the last problem to vercome before I go live). I have the following line in a macro: F_REF.Range("Y54:AE72").Sort Key1:=F_REF.Range("AE54"), Order1:=xlAscending, Key2:=F_REF.Range("AB54"), Order2:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal Most often when this line is run automatically, it doesn't sort, but there are times when it does (rare). If I step through the code then the sort works EVERY time. Similarly, if I execute the macro manually by clicking a text box, it works every time. I've tried it with calc Auto/Manual and it makes no difference whatsoever. I am at a complete loss with this. I cannot understand how it can work manually but not as part of a macro that runs it automatically. I am working with the same test data set in every instance (i.e. when it works and when it doesn't). Do you have any idea what could be causing this, and if not how I could work around it please? I've tried putting just the sort line into a separate macro, and that worked for a little while, but now that too fails to work ( I thought I had it licked with a work-around but not to be). Regards, Brett |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Modules - referring to workbook properties
Brett,
I normally find that when things work manually but not automatically it is down to objects that are not fully qualified, because I select the sheet or whatever when stepping it. But, assuming that F_REF is a worksheet object, your code looks fully qualified so it would not appear to be the problem. Without the context, it is hard to offer any other advice. -- __________________________________ HTH Bob "Brett" wrote in message ... Hi Bob I posted a question yesterday but haven't had any takers, and now it's becomng urgent (it's the last problem to vercome before I go live). I have the following line in a macro: F_REF.Range("Y54:AE72").Sort Key1:=F_REF.Range("AE54"), Order1:=xlAscending, Key2:=F_REF.Range("AB54"), Order2:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal Most often when this line is run automatically, it doesn't sort, but there are times when it does (rare). If I step through the code then the sort works EVERY time. Similarly, if I execute the macro manually by clicking a text box, it works every time. I've tried it with calc Auto/Manual and it makes no difference whatsoever. I am at a complete loss with this. I cannot understand how it can work manually but not as part of a macro that runs it automatically. I am working with the same test data set in every instance (i.e. when it works and when it doesn't). Do you have any idea what could be causing this, and if not how I could work around it please? I've tried putting just the sort line into a separate macro, and that worked for a little while, but now that too fails to work ( I thought I had it licked with a work-around but not to be). Regards, Brett |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Modules - referring to workbook properties
Hi Bob, yes, F_REF is a sheet, and that reference is used several times
before the sort. What I'm saying is that if I put a breakpoint on the sort and then press F8 to step through the code it works. Now then, since I sent my original Q to you this morning (Sydney time) I've discovered the most bizarre thing. If the cells that are being sorted are hidden then the sort fails. If they are not hidden it works (with the automatic macro as well). When I was stepping through the code I had the rows/cols unhidden so that I could see what was going on (with a split screen) and was thus able to detirmine that the code actually does work in "manual". Of course every time it runs on auto the cells are hidden, and that's when it fails. So, to counteract this problem what I have to do is unhide rows/cols before the sort and then re-hide, and it works every time.That's not normal behaviour is it? I mean, for crying out loud, you have to be able to sort a hidden range, especially given that you can select a hidden range. Just to clarify, when I run it manually I'm not using other lines of code - they are the very same lines, not even copied to somewhere else. BTW, a) the curry was good, b) my partner tracked the mohican cartoon on YouTube at http://www.youtube.com/watch?v=bHCpdXsn-NY I hadn't seen it for years. "Bob Phillips" wrote: Brett, I normally find that when things work manually but not automatically it is down to objects that are not fully qualified, because I select the sheet or whatever when stepping it. But, assuming that F_REF is a worksheet object, your code looks fully qualified so it would not appear to be the problem. Without the context, it is hard to offer any other advice. -- __________________________________ HTH Bob "Brett" wrote in message ... Hi Bob I posted a question yesterday but haven't had any takers, and now it's becomng urgent (it's the last problem to vercome before I go live). I have the following line in a macro: F_REF.Range("Y54:AE72").Sort Key1:=F_REF.Range("AE54"), Order1:=xlAscending, Key2:=F_REF.Range("AB54"), Order2:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal Most often when this line is run automatically, it doesn't sort, but there are times when it does (rare). If I step through the code then the sort works EVERY time. Similarly, if I execute the macro manually by clicking a text box, it works every time. I've tried it with calc Auto/Manual and it makes no difference whatsoever. I am at a complete loss with this. I cannot understand how it can work manually but not as part of a macro that runs it automatically. I am working with the same test data set in every instance (i.e. when it works and when it doesn't). Do you have any idea what could be causing this, and if not how I could work around it please? I've tried putting just the sort line into a separate macro, and that worked for a little while, but now that too fails to work ( I thought I had it licked with a work-around but not to be). Regards, Brett |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Class Modules - referring to workbook properties
Seems reasonable to me, sorting is a visual thing. Just like a date of
10-Dec-2009 is the visual form of a date number, data on a spreadsheet is the visual form of the underlying values. If it is hidden, it is for a reason, so presumably shouldn't be messed with. Anyway, the resolution is so simple. I don't like Thai food. I love curry, but not Thai curry. I find it too sickly sweet. -- __________________________________ HTH Bob "Brett" wrote in message ... Hi Bob, yes, F_REF is a sheet, and that reference is used several times before the sort. What I'm saying is that if I put a breakpoint on the sort and then press F8 to step through the code it works. Now then, since I sent my original Q to you this morning (Sydney time) I've discovered the most bizarre thing. If the cells that are being sorted are hidden then the sort fails. If they are not hidden it works (with the automatic macro as well). When I was stepping through the code I had the rows/cols unhidden so that I could see what was going on (with a split screen) and was thus able to detirmine that the code actually does work in "manual". Of course every time it runs on auto the cells are hidden, and that's when it fails. So, to counteract this problem what I have to do is unhide rows/cols before the sort and then re-hide, and it works every time.That's not normal behaviour is it? I mean, for crying out loud, you have to be able to sort a hidden range, especially given that you can select a hidden range. Just to clarify, when I run it manually I'm not using other lines of code - they are the very same lines, not even copied to somewhere else. BTW, a) the curry was good, b) my partner tracked the mohican cartoon on YouTube at http://www.youtube.com/watch?v=bHCpdXsn-NY I hadn't seen it for years. "Bob Phillips" wrote: Brett, I normally find that when things work manually but not automatically it is down to objects that are not fully qualified, because I select the sheet or whatever when stepping it. But, assuming that F_REF is a worksheet object, your code looks fully qualified so it would not appear to be the problem. Without the context, it is hard to offer any other advice. -- __________________________________ HTH Bob "Brett" wrote in message ... Hi Bob I posted a question yesterday but haven't had any takers, and now it's becomng urgent (it's the last problem to vercome before I go live). I have the following line in a macro: F_REF.Range("Y54:AE72").Sort Key1:=F_REF.Range("AE54"), Order1:=xlAscending, Key2:=F_REF.Range("AB54"), Order2:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal Most often when this line is run automatically, it doesn't sort, but there are times when it does (rare). If I step through the code then the sort works EVERY time. Similarly, if I execute the macro manually by clicking a text box, it works every time. I've tried it with calc Auto/Manual and it makes no difference whatsoever. I am at a complete loss with this. I cannot understand how it can work manually but not as part of a macro that runs it automatically. I am working with the same test data set in every instance (i.e. when it works and when it doesn't). Do you have any idea what could be causing this, and if not how I could work around it please? I've tried putting just the sort line into a separate macro, and that worked for a little while, but now that too fails to work ( I thought I had it licked with a work-around but not to be). Regards, Brett |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Class modules: parametrize class object fields | Excel Programming | |||
Basic question - modules and class modules - what's the difference? | Excel Programming | |||
Class Modules | Excel Programming | |||
Class Modules | Excel Programming | |||
Class Modules vs Modules | Excel Programming |