Re: $40 Help getting Excel to run correctly again
Good question. I believe almost all of the custom ribbon buttons were created through the excel UI, but a few might have created a different way via some macro that I had developed. Since my last time loggin in here, I removed MS office and then eventually reinstalled windows. I am still in the process of creating new ribbon buttons as I need them.
Re: $40 Help getting Excel to run correctly again
Even if I manually change the path to ...personal.xlsb!somemodulename.somesubroutinename it still gives me the same error
Re: $40 Help getting Excel to run correctly again
Oops sorry, the actual location is
[FONT="]C:\Users\Mach\AppData\Roaming\Microsoft\Excel\XLSTART\[/FONT][FONT=&]personal.xlsb!somesubroutinename[/FONT]
Re: $40 Help getting Excel to run correctly again
It looks like I was mistaken in my description of the problem yesterday. I looked at the path of a ribbon button macro that did still work, and ones that do not work and they both showed the same type of path
[FONT="]C:\Users\someusername\AppData\Roaming\Microsoft\Excel\personal.xlsb!somesubroutinename
None of the subroutine names matched the name of the module they were located in. It is unlikely that any of the subroutine names, at least the ones that are ribbon buttons, have the same subroutine name as found in another module. However there are multiple copies of personal.xlsb located in backup subfolders within that folder \Excel
[/FONT]
Re: $40 Help getting Excel to run correctly again
10% has been paid via paypal
When I push one of the custom ribbon buttons I have, on some, but not all of the buttons I get "cannot run the macro 'personal.xlsb!somesubroutinename. The macro may not be available in this workbook or all macros may be disabled".
For the buttons that do work (where the macro does run), to me it looks like the path is personal.xlsb!modulename.subroutinename. It is my guess that somehow the modulename's were removed from the path that many of the custum buttons point to.
Not sure what to do on this one, any help appreciated. I have reinstalled Excel once or twice with no luck.
Re: Sort Array Of Shapes by .TOP
Thank you very much, that seemed to work great. I am still having trouble with the program, not the part you wrote though and may post back later on.
I am stuck on this problem and know I am missing some key point since it has been awhile since I have done any VBA programming.
I have an array of type Shape
Arr1(1)=shapeA
Arr1(2)=shapeB
Arr1(3)=shapeC
etc
I want to create an array Arr2 so that it's results are the sort of Arr1 where Arr1 is sorted based on the .TOP position of each of it's shapes, starting with the lowest .TOP postion and increasing
Any help appreciated, thanks
Re: Linked Lists
Link the the node diargrams for sReverse
Re: Linked Lists
I thought I would work on the Linked Lists project tonight and created 1 new method - sReverse. The sReverse method reverse the order of the linked list that sList points to, in other words it just reverse the order of a linked list. Not an easy method to write for some reason.
Sub test()
'This sub tests the sReverse method of the sList class
Dim i As Long
Dim q As sList
Set q = New sList
For i = 1 To 100
q.sInsertBack (i)
Next i
q.sPrintList
q.sReverse
q.sPrintList
End SubSub sReverse()
'This method reverses a singly linked list
'
'Case - Linked list has 1 or fewer nodes
' Do nothing, exit method
'
'Case - Linked list has more than 1 node
' 1. Declare a variable called currentNode of type sListNode and point it to the same sListNode object that pHead points to
' 2. Declare a variable nextNode of type sListNode and point it to the Next field of the sListNode object that pHead points to
' 3. Declare a variable loopNode of type sListNode, point it to Nothing
' 4. While nextNode is not Nothing
' 4.1 currentNode.next = loopNode
' 4.2 loopNode = currnentNode
' 4.3 currentNode = nextNode
' 4.4 nextNode = nextNode.next
' 5. Set pHead to point to currentNode
' 6. Set the Next of the sListNode object that pHead points to to be loopNode
Dim currentNode As sListNode
Dim nextNode As sListNode
Dim loopNode As sListNode
Set currentNode = pHead
Set nextNode = pHead.sGetNext
Set loopNode = Nothing
If pSize <= 1 Then
Exit Sub
End If
Do While Not nextNode Is Nothing
Set currentNode.sSetNext = loopNode
Set loopNode = currentNode
Set currentNode = nextNode
Set nextNode = nextNode.sGetNext
Loop
Set pHead = currentNode
Set pHead.sSetNext = loopNode
End SubRe: Linked Lists
Not sure what happened with the code above, here is the test code
Sub test3333()
Dim qArr() As sList
Dim i As Long
Dim j As Long
Dim k As Long
k = 10000
ReDim qArr(1 To k)
For i = 1 To k
Set qArr(i) = New sList
For j = 1 To 2500
qArr(i).sInsertFront (CStr(j))
Next j
Next i
Stop
End SubRe: Linked Lists
[FONT="]It looks like it only crashes only if an individual sList Object contains more than about 2500 nodes. I was able to create a total of 25,000,000 sListNode objects contained in 10,000 sList Objects without excel crashing. I would guess that most data structures that use singly linked lists do not put thousands of data points within one sList object.[/FONT]
Sub test3333()
Dim qArr() As sList
Dim i As Long
Dim j As Long
Dim k As Long
k = 10000
ReDim qArr(1 To k)
For i = 1 To k
Set qArr(i) = New sList
For j = 1 To 2500
qArr(i).sInsertFront (CStr(j))
Next j
Next i
Stop
End SubRe: Linked Lists
I forgot to mention, the method sGetNth was a difficult one for me to write. I tried to do the recursive solution that seems to be commonly taught. I could not find a way to do it unless I had a private helper function in order for the recursion to work. Any ideas suggestions to improve the code for any of the methods is appreciated. Also I know I a missing at least 2 common methods which I think are sInsertAfter and sInsertBefore. Any others?
Re: Linked Lists
Here is the code that I have so far, minus commenting. There are 7 methods listed below
| sInsertFront | Fast |
| sInsertBack | Slow |
| sGetLastItem | Slow |
| sGetFirstItem | Fast |
| sGetNth | Slow |
| sLength | Fast |
| sPrintList | Slow |
sInsertFront seems to crash Excel if more than about 4000 nodes are inserted. If you have a for next loop with variable i, that keeps using sInsertFront, how would you trap the i when excel crashes?
Sometimes I will get "Out of Stack Space" error before the crash. What are the limits of the Stack Space? If Instead of one sList node with say 30,000 nodes, would 10 sList nodes of 3000 nodes each prevent the stack space error?
Other than that the code seems to work ok. I actually used it in a different program and was suprised how well it worked, it was useful.
Thanks
sListNodeClass
Option Explicit
Private pdata As String
Private pnext As sListNode
Property Let sSetData(value As String)
pdata = value
End Property
Property Get sGetData() As String
sGetData = pdata
End Property
Property Set sSetNext(value As sListNode)
Set pnext = value
End Property
Property Get sGetNext() As sListNode
Set sGetNext = pnext
End PropertysList Class
Option Explicit
Private pSize As Long
Private pHead As sListNode
Sub sInsertFront(ByRef sListNodeCargo As String)
Dim aNewNode As sListNode
Set aNewNode = New sListNode
aNewNode.sSetData = sListNodeCargo
If pSize = 0 Then
Set pHead = aNewNode
Else
Set aNewNode.sSetNext = pHead
Set pHead = aNewNode
End If
pSize = pSize + 1
End Sub
Sub sInsertBack(ByRef sListNodeCargo As String)
Dim aNewNode As sListNode
Dim FindLastNode As sListNode
Set aNewNode = New sListNode
aNewNode.sSetData = sListNodeCargo
If pSize = 0 Then
Set pHead = aNewNode
Else
Set FindLastNode = pHead
Do While Not FindLastNode.sGetNext Is Nothing
Set FindLastNode = FindLastNode.sGetNext
Loop
Set FindLastNode.sSetNext = aNewNode
End If
pSize = pSize + 1
End Sub
Function sGetLastItem() As String
Dim sGetLastNode As sListNode
Set sGetLastNode = pHead
Do While Not sGetLastNode.sGetNext Is Nothing
Set sGetLastNode = sGetLastNode.sGetNext
Loop
sGetLastItem = sGetLastNode.sGetData
End Function
Function sGetFirstItem() As String
sGetFirstItem = pHead.sGetData
End Function
Function sGetNth(ByRef NthItem As Long) As String
Dim tNode As sListNode
Set tNode = pHead
sGetNth = sGetNthHelper(NthItem, tNode)
End Function
Private Function sGetNthHelper(ByRef Nth As Long, ByRef TempNode As sListNode) As String
If Nth < 1 Or Nth > pSize Then
Stop
End If
If Nth = 1 Then
sGetNthHelper = TempNode.sGetData
Exit Function
End If
Set TempNode = TempNode.sGetNext
sGetNthHelper = sGetNthHelper(Nth - 1, TempNode)
End Function
Function sLength() As Long
sLength = pSize
End Function
Function sPrintList()
Dim i As Long
Dim String1 As String
Dim StringOld As String
Dim TempNode As sListNode
Set TempNode = pHead
For i = pSize To 1 Step -1
String1 = " " & sGetNth(i) & " " & StringOld
StringOld = String1
Next i
MsgBox String1
End FunctionRe: Linked Lists
Thanks for pointing that out. I have a lot of GB's of RAM in this machine, I wonder how much memory could a million nodes take, or how much memory is typically allocated to an instance of Excel? I got the garbage collection reason from the video CS 61B on youtube, but that was for Java. For me the important thing was that you lose access to the rest of the linked list. How could error handling be set up for sInsertFront? I do not usually include error handling in my code.
Also I see from the code that I was still having trouble with the concept of encapsulation when I posted it. The method sGetLastNode returns an sListNode which I now realize is not a good thing. I think it would have been helpful to clearly understand what the goal of the encapsulation was in the first place for this case - to prevent the user from having the ability to access any nodes at all in any way. As far as the user is concerned, they just want a list and some methods to manipulate the list. I think the method is supposed to be called sGetLastItem, not sGetLastNode, and it is supposed to return the "cargo" of the last sListNode, not the node itself.
Re: Linked Lists
The diagram for the sInsertFront method of the sList class
Re: Linked Lists
I had some time to work on the Linked List project. Code below. I was stuck on the concept of encapsulation for quiet awhile. I would make the sList class and then try to pass an sListNode Object to one of the sList's methods as a parameter. That seemed to defeat the purpose of encapsulation, finally I figured out that I did not need to pass an sListNode Object as a parameter, but only the "cargo" of that obect and just let the sList class handling creating the sListNode Objects as needed. I still have more methods to write for this class but I think I should not have to much trouble from this point.
The sList class. All the methods that work with sListNode Objects are in this class. 3 have been written so far.
Option Explicit
'The sList class is created to address at least 2 scenarios with attempting methods directly in the sListNode class
'1. If two variables, x and y point to the same SListNode (linked list), then operations will change sListNode for one variable but not the other
'Set x = New sListNode("Soap",x) ==> A new sListNode is created. The item is set to "Soap". The Next is set to point to the old value of x. Finally x is assigned to this n ew list
'x and y are now two different linked lists
'2. How would you represent an empty list? If an empty list is represented by Nothing (null in Java), you will get a run time error by calling a sListNode method on x.
'The solution to these two problems in Java (and maybe VBA) is a separate sList class (as opposed to the sListNode class). The job of the sList class is to maintain the
'head of the list in a separate data structure. The Head is similar to a sListNode, except that instead of the item (field?), it has a Size field. Instead of a Next field
'it has a field called Head.
'When initialized by for example set q=New sList, pSize will be 0 and pHead will be Nothing. The New keyword causes an object to be created from sList. As drawn
'that object is simply drawn schematically as square or rectangle to represent a "blank" object. Private pSize and Private pHead below create a property each of that object. After
'Those two statements, there now an object that is no longer "blank", it actually now has two properties by default pSize is set to 0 and pHead is set to Nothing.
'These properties are created whenever the sList is initialized or created for the first time. The methods below (either Sub subname() or Function FunctionName() are
'Not called when the object is initialized for the first time. They are only called explicitly, sListObject.SomeMethod
'This sList object is NOT part of the linked list
'The head property of an sList always points to a Linked List, even for an empty linked list. Null or Nothing is an empty linked list.
Private pSize As Long
Private pHead As sListNode
'This sList class encapsulates the sListNode class and prevents the user
'from being able to directly access its properties or methods
'as well as solves the two problems mentioned in #1 and #2 above.
'Method sInsertFront will insert an sListNode object at the front of the linked list that pHead points to
'These methods should be sketched out on paper. Note that for any object, it must be pointed to in order to "exist". If during the process
'of rearranging pointers, a linked list even momentarily is not "pointed to", it will be lost and garbage collected. So the order
'in which pointers are rearranged is important
'Note that sInsertFront does not take in an sListNode as a variable, although it could. An sList Object is designed to encapsulate an sListNode Object
'the user should not need or be able to to directly work with sListNode's properties or methods in their code.
'Only the "cargo" of an sListNode is sent as a parameter. An sList Object will take care of creating all sListNode Object(s) and
'putting the parameter "cargo" in the item property of an sListNode Object. For now, it is assumed that the pData property (Cargo) of an sListNode Object
'is always a string. Later it may be possible to chagne it to type Variant or Object.
Sub sInsertFront(ByRef sListNodeCargo As String)
'It is possible to have the parameter to an sListNode object, but that defeats the purpose of encapsulation, I think.
'The user should not be directly working with an sListNode's properties or methods. Once these Classes are finished, the
'user should not need to work directly with Nodes at all.
Dim aNewNode As sListNode
Set aNewNode = New sListNode
aNewNode.sSetData = sListNodeCargo
If pSize = 0 Then
Set pHead = aNewNode
Else
Set aNewNode.sSetNext = pHead
Set pHead = aNewNode
End If
pSize = pSize + 1
End Sub
Function sGetLastNode() As sListNode
'Gets the last sListNode Object in the linked list pointed to by this instance of the sList Object's pHead property
'Noted that me.pHead does not seem to work, not sure why. Using pHead instead of me.pHead seems to work.
Set sGetLastNode = pHead
Do While Not sGetLastNode.sGetNext Is Nothing
Set sGetLastNode = sGetLastNode.sGetNext
Loop
End Function
Function sLength() As Long
'Returns the number of sListNode objects that are in the linked list that this instance of the sList Object's pHead property points to
sLength = pSize
End FunctionThe sListNode Class
Option Explicit
Private pdata As String
Private pnext As sListNode
Property Let sSetData(value As String)
pdata = value
End Property
Property Get sGetData() As String
sGetData = pdata
End Property
Property Set sSetNext(value As sListNode)
Set pnext = value
End Property
Property Get sGetNext() As sListNode
Set sGetNext = pnext
End PropertyThe code below just tests some of the methods of the sList class
Sub test3333()
Dim i As Long
Dim j As Long
Dim h As sListNode
Dim q As sList
Dim String1 As String
Set q = New sList
For i = 1 To 4
q.sInsertFront (CStr(i))
Next i
Set h = q.sGetLastNode
Stop
End SubRe: Linked Lists
I had the first method of sList, sInsertFront, all written and diagrammed out. sInsertFront inserts a node into the front of an existing linked list. I tested it out for 10,100,1000 sInsertFront operations via a loop and it worked great. When I tried it for 1,000,000, excel crashed and I lost all my work. I will redo it next week and post.
Re: $50 1. Excel VBA Modify field values in Outlook 2010 2. Export fields
The 10% has been paid to Ozgrid
Present user with dialog box, user will select folder of interest in Outlook 2010 There are several different fields for a ..I think mailitem? Email address, First Name, Middle Name, Last Name Find a way to identify if a field constains an email address and only an email address. Use Regex? #1 For each mailitem If the email address field does contain an email address, then overwrite Firstname, Middle Name and Last name and put the email address in there. When done, The email address field, the first name, middle name and last name will all contain the same email address. If the email address filed does not contain an email address, check if the first name, middle name or last name contain an email address. If any of those fields do contain an email addresss, copy that email address to the email field and then have the first name, middle name and last name also show that email address If nont of the fields contain an email address, export all the fields to an excel worksheet for that mailitem. When the program is finished, the user can view on the worksheet all the fields that did not show an email address. #2 Present user with dialog box again to choose a folder in Outlook 2010. Then an inputbox to enter a Search Term. Present the user with a dialog tickbox where they can check the fields they want searched for an occurance of the Search Term. If the SearchTerm is found in a mailitem in any of the user selected fields, export that data to an excel worksheet. The header values would be the name of any applicable field (all possible, not just the user selected for the search). For example this should include email address, first name, last name, body of the email, subject line, etc..