Posts by Megazoid

Megazoid · Feb 10th 2017

Re: dynamically adjust Array of Ranges

Hi S O, thank you for your answer.

I understand what you have indicated and is the better way to proceed

Megazoid

Megazoid · Feb 6th 2017

Hi, I wonder if anyone can help, I am not sure what I am considering can even be done.
I have a function which returns a single cell range or a group of cell ranges as a range.
I was wondering if I could seek guidance on how I could have an array of Ranges to store results for later use if the function is used several times as part of a loop.

I cannot see how I could Redim this array if used dynamically?

Code

Dim Results() As Range
Dim I as integer
Dim MyRange as Range
'...
Redim Preserve Results(1 to I)
set Results(I) = MyRange
I=I+1
'....

Any suggestions please.

Thanks

Megazoid

Megazoid · Mar 29th 2016

Re: Hyperlink from one workbook to another

HI Ger, thanks again for the tip about spaces in Sheet Names. I have already encountered this little problem and am dealing with it in my code. I Check all sheet names and substitute all spaces with an underscore.

Happy Easter to you.

Best Wishes

Megazoid

Megazoid · Mar 28th 2016

Re: Hyperlink from one workbook to another

Hi Ger, Thanks for the Reply. I understand this now i.e use subaddress.

Have this working fine.

Where I Have a range of Full Paths in Col A, Sheet Names in Col B and Cell References in Col C Then

Code Snippet:

Code

Dim MyRange as Range
Dim MyCell as Range
Dim LastRow as Long
Dim FirstRow as Long


Set MyRange = Range(Cells(FirstRow,1),Cells(LastRow,1))
For Each Mycell in MyRange
MyCell.Hyperlinks.Add Anchor:=Mycell, Address:=Mycell.Value, Subaddress:= Mycell.Offset(0,1).Value & "!" & Mycell.Offset(0,2).Value, ScreenTip:="This will open another workbook!"
Next Mycell

Many Thanks

Megazoid

Megazoid · Mar 23rd 2016

Hi Guys, a pointer please. I want to create a hyperlink from one workbook to a cell in another book.
At the time of creation, both books are open. I am not sure of the exact syntax to use. I have been trying to get this right for some time without any luck.

I have values for the full path to the open workbook, the sheet name to which the link is to be made and also the cell address, these are stored in separate variables.

e.g C:\MyFiles\MyWorkbookName.xlsx, Sheet3, Cell f5

The method I am using is Cellx.Hyperlinks.Add , Anchor:= Cellx, Address: = ?

Any help please?

Megazoid

Megazoid · Jan 3rd 2016

Re: Application.InputBox Type 16

Hi All,
I have given some more thought to this problem and realised that the same problem is encountered with type 4, if the user closes or cancels the input.
The only way that I can think of to circumvent these issues is to treat these instances as cases of other type numbers (in this case Type and also allow for the closing of the input box by the user.

I have developed the attached code which will specify the data entered by the user and will also detect if the cancel option has been selected. This can be used for all #type.

The code may not be entirely efficient, but I am sure that readers may make suggestions to improve this aspect. The point is, however, that it appears to work.

Code

Sub ApplicationInputBoxResponse(MyPrompt As String, MyTitle As String, MyType As Integer, MyInput As Variant, MyRange As Range)
 ' MyType = 0 (Formula), MyType = 1 (Number), MyType = 2 (String), MyType = 4 (Logical Value True or False), MyType = 8 (Range),
 ' MyType = 16 (An Error Value e.g. #N/A), MyType = 64 (An Array of Values)
Dim Pos As Integer
Dim Pos1 As Integer
Dim i As Integer
Dim j As Integer
Dim MyResult As Variant
Dim MyEmpty As Boolean
 Select Case MyType
Case 0:
MyInput = Application.InputBox(Prompt:=MyPrompt, title:=MyTitle, Type:=MyType)
If MyInput <> False Then
Pos = InStrRev(MyInput, "C")
Pos1 = InStr(1, MyInput, "R")
i = Mid(MyInput, Pos1 + 1, Pos - (Pos1 + 1))
j = Mid(MyInput, Pos + 1, Len(MyInput) - Pos)
MyResult = Cells(i, j).Value
If Cells(i, j).HasFormula = True Then
MyInput = Cells(i, j).formula & " & Result of Formula = " & MyResult
Else
MyInput = False
End If
End If
Case 1:
MyInput = Application.InputBox(Prompt:=MyPrompt, title:=MyTitle, Type:=MyType)
If Not (Application.WorksheetFunction.IsNumber(MyInput) = True) Then
MyInput = False
End If
Case 2:
MyInput = Application.InputBox(Prompt:=MyPrompt, title:=MyTitle, Type:=MyType)
If Not (Application.WorksheetFunction.IsText(MyInput) = True) Then
MyInput = False
End If
Case 4:
MyType = 8
On Error Resume Next
Set MyRange = Application.InputBox(Prompt:=MyPrompt, title:=MyTitle, Type:=MyType)
On Error GoTo 0
If Not MyRange Is Nothing Then
MyInput = MyRange.Value
End If
If VarType(MyInput) = vbEmpty Then
MyEmpty = True
End If
If Not (VarType(MyInput) = vbBoolean) Then
MyInput = "A Non Boolean"
End If
MyType = 4
Case 8:
On Error Resume Next
 Set MyRange = Application.InputBox(Prompt:=MyPrompt, title:=MyTitle, Type:=MyType)
 On Error GoTo 0
 If Not MyRange Is Nothing Then
 MyInput = "Range " & Application.WorksheetFunction.Substitute(MyRange.Address, "$", "")
 Else
 MyInput = False
 End If
Case 16:
MyType = 8
On Error Resume Next
Set MyRange = Application.InputBox(Prompt:=MyPrompt, title:=MyTitle, Type:=MyType)
On Error GoTo 0
If Not MyRange Is Nothing Then
MyInput = MyRange.Value
End If
If VarType(MyInput) = vbEmpty Then
MyInput = False
End If
If (VarType(MyInput) <> vbError) And (VarType(MyInput) <> vbEmpty) And (VarType(MyInput) <> vbBoolean) Then
MyInput = "Not an Error"
End If
If IsError(MyInput) Then
Select Case MyInput
Case CVErr(xlErrNA)
    MyInput = "Cell containing #N/A error"
Case CVErr(xlErrDiv0)
    MyInput = "Cell containing #DIV/0! error"
Case CVErr(xlErrNull)
    MyInput = "Cell containing #Null!"
Case CVErr(xlErrName)
    MyInput = "Cell containing #Name? error"
Case CVErr(xlErrNum)
    MyInput = "Cell containing #Num! error"
Case CVErr(xlErrRef)
    MyInput = "Cell containing #Ref! error"
Case CVErr(xlErrValue)
    MyInput = "Cell containing #Value! error"
End Select
End If
MyType = 16
Case 64:
MyResult = Application.InputBox(Prompt:=MyPrompt, title:=MyTitle, Type:=MyType)
If IsArray(MyResult) Then
MyInput = "An Array:  {"
  For i = LBound(MyResult) To UBound(MyResult)
    MyInput = MyInput & MyResult(i) & ","
  Next i
MyInput = Left(MyInput, Len(MyInput) - 1)
  MyInput = MyInput & "}"
End If
If VarType(MyResult) = vbEmpty Then
MyInput = False
End If
End Select
 If ((MyInput = False) And (MyType <> 4)) Or (MyEmpty = True) Then
 MsgBox ("You clicked the Cancel button, Input Box will Close")
 Exit Sub
 Else
 MsgBox ("You have entered: " & MyInput)
 End If
 End Sub

Display More

Code

Public Sub ApplicationInputBoxResponseTest()
' MyType = 0 (Formula)
 ' MyType = 1 (Integer)
 ' MyType = 2 (String)
 ' MyType = 4 (Logical Value True or False)
 ' MyType = 8 (Range)
 ' MyType = 16 (An Error Value e.g. #N/A)
 ' MyType = 64 (An Array of Values)
 
Dim MyType As Integer
Dim MyVariant As Variant
Dim MyRange As Range
Dim MyPrompt As String
Dim MyTitle As String
MyPrompt = "Input Data"
MyTitle = "ApplicationInputBoxResponse"
MyType = InputBox("MyType = ")
 Call ApplicationInputBoxResponse(MyPrompt, MyTitle, MyType, MyVariant, MyRange)
 
End Sub

Display More

Enjoy Megazoid

Megazoid · Dec 30th 2015

Re: Application.InputBox Type 16

Hi Rory, Thank you. The last point you made about a cell not containing an error, I have seen happen when I have tried it myself. I guess the point is only to find the
description of the error in a cell containing an error. If there is no error then not sure what to do!! Will think about this some more.

Happy New Year to you

Megazoid

Megazoid · Dec 29th 2015

Hi, I was wondering if anybody could explain with a couple of typical examples how the Application.Inputbox is used with type:= 16 (Error).

I understand how to use all the other type variants 0,1,2,4,8,64 . There are lots of examples out there for the types I have listed, unfortunately I am having difficulty in finding specific examples for
type 16 and I cannot quite understand how to use it correctly. Just a case of my education, if somebody can help?

Many Thanks

Megazoid

Megazoid · Dec 1st 2015

Re: Excel Add In

Hi Roy, thank you for the link to Ron DeBruin's Page where he more than adequately demonstrates how to add an Icon & Macro to the Ribbon.
On consideration, I agree a ribbon icon would look more professional. I have now downloaded the UI editor. By modifying one of Ron's examples
I am sure that I will be able to create a professional looking Add-In containing both subs and UDF's.

http://www.fontstuff.com/vba/vbatut03.htm

http://www.rondebruin.nl/win/s2/win009.htm

I would like to thank you all for your tips and comments.

Best Regards

Megazoid

Megazoid · Nov 30th 2015

Re: Excel Add In

Hopefully, now corrected

Hi all, now done some research on the Menu activity.
I do not like the idea of coding the UI to modify the Ribbon. I have found some code, provided by John Walkenback in his book on Excel 2010, which I have tried and it works fine.

This code adds an Icon onto a custom menu on the Add Inn Tab, which will allow my Macro to run. The code adds an Old style (2007) menu bar and an msoImage.
This is fine as different users have different Office versions going down to 2007.

There are two elements 1) This Workbook Code and 2) Standard Module Code. I hope that John will not mind if I post it here, as this could be useful to many Excel users.

Code

Private Sub Workbook_Open()
    Call CreateToolbar
 End Sub
 Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call DeleteToolbar
 End Sub

This WorkBook Code:

ModuleCode:

Code

Sub CreateToolbar()
    Dim TBar As CommandBar
    Dim Btn As CommandBarButton


 '   Delete existing toolbar (if it exists)
    On Error Resume Next
    CommandBars(TOOLBARNAME).Delete
    On Error GoTo 0


 '   Create toolbar
    Set TBar = CommandBars.Add
    With TBar
        .Name = TOOLBARNAME
        .Visible = True
    End With


 '   Add a button
    Set Btn = TBar.Controls.Add(Type:=msoControlButton)
    With Btn
       .FaceId = 300
       '.Picture = Application.CommandBars.GetImageMso("ViewAppointmentInCalendar", 16, 16)
       .OnAction = "Macro1"
       .Caption = "Macro1 Tooltip goes here"
    End With
 '   Add another button
    Set Btn = TBar.Controls.Add(Type:=msoControlButton)
    With Btn
       .FaceId = 25
       '.Picture = Application.CommandBars.GetImageMso("CDAudioStopTime", 16, 16)
       .OnAction = "Macro2"
       .Caption = "Macro2 Tooltip goes here"
    End With
 End Sub
 Sub DeleteToolbar()
    On Error Resume Next
    CommandBars(TOOLBARNAME).Delete
    On Error GoTo 0
 End Sub

Display More

Any comments or lessons on UI program would prove useful, for possible future considerations.

Best Wishes to all

Megazoid

Megazoid · Nov 30th 2015

Re: Excel Add In

Sorry, not sure why the # code wrap did not work.

Apologies

Megazoid

Megazoid · Nov 30th 2015

Re: Excel Add In

Hi all, now done some research on the Menu activity.
I do not like the idea of coding the UI to modify the Ribbon. I have found some code, provided by John Walkenbach in his book on Excel 2010, which I have tried and it works fine.

This code adds an Icon onto a custom menu on the Add Inn Tab, which will allow my Macro to run. The code adds an Old style (2007) menu bar and an msoImage.
This is fine as different users have different Office versions going down to 2007.

There are two elements 1) This Workbook Code and 2) Standard Module Code. I hope that John will not mind if I post it here, as this could be useful to many Excel users.

This WorkBook Code:

Code

[Private Sub Workbook_Open()
Call CreateToolbar
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DeleteToolbar
End Sub

Module Code:

Code

[Sub CreateToolbar()
    Dim TBar As CommandBar
    Dim Btn As CommandBarButton


 '   Delete existing toolbar (if it exists)
    On Error Resume Next
    CommandBars(TOOLBARNAME).Delete
    On Error GoTo 0


 '   Create toolbar
    Set TBar = CommandBars.Add
    With TBar
        .Name = TOOLBARNAME
        .Visible = True
    End With


 '   Add a button
    Set Btn = TBar.Controls.Add(Type:=msoControlButton)
    With Btn
       .FaceId = 300
       '.Picture = Application.CommandBars.GetImageMso("ViewAppointmentInCalendar", 16, 16)
       .OnAction = "Macro1"
       .Caption = "Macro1 Tooltip goes here"
    End With
 '   Add another button
    Set Btn = TBar.Controls.Add(Type:=msoControlButton)
    With Btn
       .FaceId = 25
       '.Picture = Application.CommandBars.GetImageMso("CDAudioStopTime", 16, 16)
       .OnAction = "Macro2"
       .Caption = "Macro2 Tooltip goes here"
    End With
 End Sub
 Sub DeleteToolbar()
    On Error Resume Next
    CommandBars(TOOLBARNAME).Delete
    On Error GoTo 0
 End Sub

Display More

Any comments or lessons on UI program would prove useful, for possible future considerations.

Best Wishes to all

Megazoid

Megazoid · Nov 26th 2015

Re: Excel Add In

OK, thanks. So I will try creating an addin with a sub, following the same process as for a function.I will add a menu item for this. If I have any difficulties, I may come back for some further advice.

Thanks

Megazoid

Megazoid · Nov 26th 2015

Re: Excel Add In

Further Point, actually, I guess I will only need to add the name of the calling Sub and this will then load the userform which in turn will be used to run the subs/UDF's

Thanks

Megazoid

Megazoid · Nov 26th 2015

Re: Excel Add In

Hi Rory, good day to you. Thank you for telling me of the need for this activity. I will research how to add these routines to a menu system. I assume this will remove the need for the initial Userform interface?

Thanks

Megazoid

Megazoid · Nov 25th 2015

Re: Excel Add In

Hi S O, Thank you for the reply.
I have about 60 UDF and Subs, which I have in a Workbook module. At the moment it is defined under a single Sub Name which is called UtilityPack (Very Original, I know).
When the sub is called it opens a userform which contains 60 Checkboxes and a number of command buttons. Tick the appropriate Check Box to select the appropriate action. Press the execute command button and it performs the required task.
I have a number of colleagues at work who would also like to use this utility pack. I was wondering if the best way of doing this was via an add in. I have not previously created an add in and was unsure about the use of Subs within an Add In. Based upon your answer would there be any pit falls to watch out for?

Megazoid · Nov 25th 2015

Is it possible to create an Excel AddIn which uses a Sub rather than a UDF?

Megazoid · May 14th 2015

Re: Doolittle Algorithm for Matrix LU Decomposition

I now have a Sub which calculates the required LU Decomp for a number of input matrices which is determined by the user 1, 3, 5,....
I will post the code for others shortly.
One question which relates to output of results back to a worksheet. My LU Decomp consists of an array of arrays. i.e the LU Decomp of two input matrices is an array
of 4 matrices 2 x LT matrices and 2 x UT Matrices. ResultsArray(1 to 4) of arrays of various sizes 2x2, 5x5 etc...

How can I find the UBound of the sub arrays ? and the UBound of the ResultsArray ?

Thanks

Megazoid

Megazoid · May 7th 2015

Re: Doolittle Algorithm for Matrix LU Decomposition

Hi Pike, thanks for the pointer.

The web link you have provided will be a big help. It was primarily figuring out the looping that I was having trouble with.
I see there is a Fortran version contained within the loop algorithm listed in the article. I think I undersatand what is happening here, but not being that familiar with Fortran, I do not understand what the return statement means within the loops.

What I have so far is a Sub which will take in from an Excel sheet as many Matrices for decomposition as a user may like Matrix 1.2....etc. The user picks the matrices by Mouse selection.
I have a routine which will put the outputs (the resultant decompositions) back to a sheet at a sheet location picked by the user. I can check the input matrices for size and squareness. So I just need mainly to sort out the algorithm looping. Will work on the Fortran prog provided in your link.

I will post the code for others when I know it works correctly.

Best Wishes Megazoid.

Megazoid · May 6th 2015

Hi Guys, I am in the process of producing a number of Matrix related functions and subs.

I would like to obtain a VBA version of the Doolittle Algorithm for decomposing a square matrix into its Lower and Upper Triangular forms. Ideally, it does need to be the Doolittle algorithm rather than Crouts method or the Cholesky method.
Although I understand the method and can use it, I am having some difficulty in coding this in VBA.

Can any body help by providing some existing VBA code to perform this task.

Many thanks

Megazoid.