Free Microsoft Excel 2013 Quick Reference

Laurent Longre’s MOREFUNC.XLL

Apologies if this post is misplaced.... I'm trying to get hold of the MOREFUNC.XLL add-in, but I'm unable to locate any working sites - http://longre.free.fr/english/ seems to be down. (It could just be my work blocking access, for some reason.)

Is it possible for someone to post this file elsewhere, or email it to me? I don't think this request is against the rules as the add-in is free, but mods feel free to close if you disagree.

Thanks


Post your answer or comment

comments powered by Disqus
To make matters it a bit clearer I thought it would be better to start a new
thread about this.
Previous thread was: Fastest way to sort large 2-D arrays?

I am trying to figure out how to use Laurent Longre's VSORT in the .xll
add-in MoreFunc.
The help file makes it clear for ranges, but not for VBA arrays.
Say I have a 10 column array that I want sorted ascending on column 2 and
descending on column 5
what would the syntax be for that?

The only thing that I have got working sofar is this:

Sub Test()

Dim arr(1 To 10000, 1 To 5) As Long
Dim arr2
Dim i As Long
Dim c As Long

For i = 1 To 10000
arr(i, 1) = Int((i * Rnd) + 1)
For c = 2 To 5
arr(i, c) = i
Next
Next

arr2 = Application.Run([VSORT], arr, arr, 0) 'this works

End Sub

This will sort descending on column 1.

Thanks for any advice.

RBS

I use MCONCAT (Laurent Longre's MOREFUNC.XLL). I often need to filter my
spreadsheet and work with only a portion of the data. I was hoping there was
a way to use the MCONCAT to concatenate only the "visible cells".

My spreadsheet looks like this:

ColA ColB
AB 123
CD 456
EF 123

If I filter ColB down to 123, I would like the MCONCAT to concatenate ColA
Values AB and EF.

Thank you for thinking about this.

Best Regards.

I use MCONCAT (Laurent Longre's MOREFUNC.XLL). I often need to filter my
spreadsheet and work with only a portion of the data. I was hoping there was
a way to use the MCONCAT to concatenate only the "visible cells".

My spreadsheet looks like this:

ColA ColB
AB 123
CD 456
EF 123

If I filter ColB down to 123, I would like the MCONCAT to concatenate ColA
Values AB and EF.

Thank you for thinking about this.

Best Regards.

This is a bit of a complex question, but let me try.

Firstly, I picked up a really neat trick from Jim Mack to alter the lBound
of an array.
Maybe one for Alan Beban, but I suppose the knows this already.
This is the Sub I use now as the result of this tip:

Private Declare Function VarPtrAry _
Lib "msvbvm60" _
Alias "VarPtr" (Ary() As Any) As Long
Private Declare Sub CopyMemory _
Lib "kernel32" _
Alias "RtlMoveMemory" (Dest As Any, Src As Any, _
ByVal cBytes As Long)

Function GetArrayDims(arr As Variant) As Integer

'---------------------------------------'
'copied from Francesco Balena at: '
'http://www.devx.com/vb2themax/Tip/18265'
'---------------------------------------'
Dim ptr As Long
Dim VType As Integer
Const VT_BYREF = &H4000&

' get the real VarType of the argument
' this is similar to VarType(), but returns also the VT_BYREF bit
CopyMemory VType, arr, 2

' exit if not an array
If (VType And vbArray) = 0 Then
Exit Function
End If

' get the address of the SAFEARRAY descriptor
' this is stored in the second half of the
' Variant parameter that has received the array
CopyMemory ptr, ByVal VarPtr(arr) + 8, 4

' see whether the routine was passed a Variant
' that contains an array, rather than directly an array
' in the former case ptr already points to the SA structure.
' Thanks to Monte Hansen for this fix

If (VType And VT_BYREF) Then
' ptr is a pointer to a pointer
CopyMemory ptr, ByVal ptr, 4
End If

' get the address of the SAFEARRAY structure
' this is stored in the descriptor

' get the first word of the SAFEARRAY structure
' which holds the number of dimensions
' ...but first check that saAddr is non-zero, otherwise
' this routine bombs when the array is uninitialized
' (Thanks to VB2TheMax aficionado Thomas Eyde for
' suggesting this edit to the original routine.)
If ptr Then
CopyMemory GetArrayDims, ByVal ptr, 2
End If

End Function

Sub SetLBound(Ary() As Double, lNewLBound As Long)
'
' "As Double" for example only -- use your specific type
'
' Note that this won't work for string() or UDT() with strings
'
' Sets Ary's LBound to NewBound, returns previous LBound.

Dim i As Integer
Dim AryPtr As Long
Dim PrevLBound As Long
Dim iDims As Integer

iDims = GetArrayDims(Ary)

If iDims = 0 Then
Exit Sub
End If

AryPtr = VarPtrAry(Ary) ' address of address of safearray struct

CopyMemory AryPtr, ByVal AryPtr, 4

AryPtr = AryPtr + 20 ' pointer to safearray.bounds.lLbound

CopyMemory PrevLBound, ByVal AryPtr, 4

'no point altering lBound to the existing lBound
If PrevLBound = lNewLBound Then
Exit Sub
End If

For i = 1 To iDims
CopyMemory ByVal AryPtr + (i - 1) * 8, lNewLBound, 4
Next

End Sub

The last Sub needs to be specific for a specific datatype, so if you gave it
an array of longs it would be:
Sub SetLBound(Ary() As Long, lNewLBound As Long)

Now I thought I could use this trick (altering the lBound of an array much
faster than by any other method)
to handle a little problem with the VSort function in Laurent Longre's .xll
MoreFunc.
This is that it alters 0-based arrays in 1-based arrays.

This is the wrapper function for this VSort as I have it now:

Function VSORTArray(ByRef arr As Variant, _
ByVal btCol1 As Byte, _
ByVal strSortType1 As String, _
Optional ByVal btCol2 As Byte = 0, _
Optional ByVal strSortType2 As String = "", _
Optional ByVal btCol3 As Byte = 0, _
Optional ByVal strSortType3 As String = "") As Variant

'------------------------------------------------------------------
'http://longre.free.fr/english/
'Uses Laurent Longre's VSort function in the .xll add-in MoreFunc
'Will be about 4 to 5 times faster than a quicksort and can sort
'on multiple columns.
'Done up to 3 columns here, but can be done up to 14 columns
'------------------------------------------------------------------
'will sort an 0-based or 1-based 2-D array with up to 3 sort keys
'the field key has to be supplied as a byte, where the first column
'of the array is 1, even if it is an 0-based array
'the sort type has to be given as "a", "A" , "b" or "B"
'examples:
'sorting on 1 field: arr2 = VSORTArray(arr, 1, "A")
'sorting on 2 fields: arr2 = VSORTArray(arr, 2, "D", 5, "A")
'------------------------------------------------------------------

Dim i As Long
Dim c As Long
Dim LB1 As Long
Dim UB1 As Long
Dim LB2 As Long
Dim UB2 As Long
Dim arrKey1
Dim arrKey2
Dim arrKey3
Dim btSortType1 As Byte
Dim btSortType2 As Byte
Dim btSortType3 As Byte
Dim arrFinal
Dim arrFinal2

LB1 = LBound(arr)
UB1 = UBound(arr)
LB2 = LBound(arr, 2)
UB2 = UBound(arr, 2)

'make the array for key 1
'------------------------
ReDim arrKey1(LB1 To UB1, LB1 To LB1)
For i = LB1 To UB1
arrKey1(i, LB1) = arr(i, btCol1 - (1 - LB1))
Next

'set the sort type for key 1
'---------------------------
If UCase(strSortType1) = "A" Then
btSortType1 = 1
Else
btSortType1 = 0
End If

If Not btCol2 = 0 Then
'make the array for key 2
'------------------------
ReDim arrKey2(LB1 To UB1, LB1 To LB1)

For i = LB1 To UB1
arrKey2(i, LB1) = arr(i, btCol2 - (1 - LB1))
Next

'set the sort type for key 2
'---------------------------
If UCase(strSortType2) = "A" Then
btSortType2 = 1
Else
btSortType2 = 0
End If
End If

If Not btCol3 = 0 Then
'make the array for key 3
'------------------------
ReDim arrKey3(LB1 To UB1, LB1 To LB1)
For i = LB1 To UB1
arrKey3(i, LB1) = arr(i, btCol3 - (1 - LB1))
Next

'set the sort type for key 3
'---------------------------
If UCase(strSortType3) = "A" Then
btSortType3 = 1
Else
btSortType3 = 0
End If
End If

If Not strSortType3 = "" Then
'3 fields to sort on
'-------------------
arrFinal = Application.Run([VSORT], arr, _
arrKey1, btSortType1, _
arrKey2, btSortType2, _
arrKey3, btSortType3)
Else
'2 fields to sort on
'-------------------
If Not strSortType2 = "" Then
arrFinal = Application.Run([VSORT], arr, _
arrKey1, btSortType1, _
arrKey2, btSortType2)
Else
'1 field to sort on
'------------------
arrFinal = Application.Run([VSORT], _
arr, arrKey1, btSortType1)
End If
End If

If LB1 = 0 Then
'to revert back to an 0-based array
'----------------------------------
ReDim arrFinal2(LB1 To UB1, LB2 To UB2)
For i = LBound(arrFinal) To UBound(arrFinal)
For c = LBound(arrFinal, 2) To UBound(arrFinal, 2)
arrFinal2(i - (1 - LB1), c - (1 - LB2)) = arrFinal(i, c)
Next
Next
VSORTArray = arrFinal2
Else
VSORTArray = arrFinal
End If

End Function

This works fine, but to be able to use the above lBound altering code when
giving it an array of longs,
arrFinal needs to be declared as an array of longs.
This now is the trouble. Tried all sorts of constructions, but sofar haven't
managed it yet.
It looks VSort needs to be used as a function, so with the sorted array as
the function result.
Running it as a Sub doesn't work.
Any suggestions how to do this?

RBS

Does anyone know the details about the add-in morefunc.xll? I was recommended this add-in and I can't seem to access the web site. Has anyone been to the web site: http://longre.free.fr/english/index.html ?
When I try to access it, the web site has been blocked. Any info about the add-in or web site would be a great help.
Thanks.

Good day. This message was originally posted by error in the Lounge. How that happened, I have yet to figure out. Anyway, I recently posted a problem and the answer was to download the INDIRECT.EXT from Laurent Longre's website. I have tried Googling Longre, but every time that I get to the actual download command, I get a "Forbidden" access error message. Is he off-line for some reason? How do I register for access? Does anyone know where I can get a copy of the MoreFunc download?

Thanks, Dan...

On Thu, 09 Jun 2005 08:07:25 -0700, pleduc@canada-dot-com.no-spam.invalid
(pilaar39 - ExcelForums.com) wrote:

>I am tryijng to sum the largest 4 values in a column of cells.
>
>I used:
>SUM(LARGE(A1:A10,{1,2,3,4}))
>which seems to work fine in general,
>however, if any of the top 4 largest values is the same, then it
>yeilds an incorrect sum.
>
>How can I overcome this failure? I need to include duplicate values in
>my sum.

Can you give examples of how you think it is working incorrectly?

Or is it that you want to count the 4 highest numbers but not double count
duplicates?

In that case, download and install the Laurent Longre's free morefunc.xll from
http://xcell05.free.fr/, and can use the following formula:

=SUM(LARGE(UNIQUEVALUES(A1:A10),{1,2,3,4}))

However, if there are fewer than four unique values in the range, you will get
an error message.

--ron

Hello
I have created a file that uses Longre's morefunc. I have the .xll
located in the same directory as the file. How do I check that my
user's also have the add-in enables and if not, enable it for them?
Thanks!
Steve

Are there any alternative places to download MOREFUNC.XLL? The link at the Morefunc for Excel site is broken, and the forums seem currently busted as well.

Hello all!

I'm writing a macro and I'd like to use one of the functions included in
Longre's free morefunc.xll add-in from http://xcell05.free.fr/

I'm interested in using UniqueValues but I don't manage it to work.

For example, an very simple code like this:

Sub ExtractItems()

Dim i As Integer
Dim list As Variant
Dim DataRange As Range

Set DataRange = Worksheets("Hoja1").Range("$A$1:$B$4")

list = uniquevalues(DataRange)

End Sub

doesn't work at all (plus, I get an error).

Sorry, I forgot to mention that I've already checked that the add-in is
properly loaded.

Can you help me with this?

Many thanks in advance.

I have a lot of code in VBA and I need to speed it up. I've done all the speed up tricks I can do in VBA and now want to put it in a DLL or XLL or something.

How can I go about this? I've read you can do XLL's in C++, but I want to keep them in VB. Is this possible? And if so, how?

Or Should I use a DLL created in VB and if so, how would I go about doing that?

Guidance and suggestions would be most appreciated.

Cash

Hi all.

Using xl 2003

Downloaded and installed Morefunc.exe from Laurent Longre
HTML Code:

Tried to insert the nbtext function
XL simply hangs
Formula bar shows = nbtext()
I cannot click on anything or proceed in anyway

Has anyone run across this?
Thanks
-marc

I had been using Harlan Grove's PULL function in order to link to data in another workbook that wasn't necessarily currently
open in Excel.

The PULL function allows you to specify the details of your linked range as a cell value (unlike a direct link), similar to the
INDIRECT function. However, INDIRECT doesn't work with closed workbooks. I like Harlan's PULL function because the code is
open and can be easily copied and pasted into additional VBA workbooks as required so that no add-ins are needed. (I have not
had much experience with using INDIRECT.EXT via the MoreFunc add-in, although it is possible to "attach" MoreFunc to a workbook
so that functions can be used without the add-in being installed.)

The PULL function works well - however, it can be slow to use when returning large ranges of cells. For example, we had a
spreadsheet that was taking 5 minutes to update when using PULL to refer to a range of 3000 cells. So I looked into creating
an alternative function that would be more suited to my circumstance. I feel I was successful and wanted to share my findings
here, since the publishing of the PULL function was very helpful to me. (Harlan - I hope that you don't mind that I have used
some of your ideas in the new function.)

The PULL function uses the ExecuteExcel4Macro command to get values from a cell from a closed workbook. The LINKEDRANGE
function that I present here differs from PULL in that it actually opens the linked workbook (in a separate Excel instance,
since spreadsheets cannot be normally be opened in a UDF), gets the values it needs and closes the workbook.

LINKEDRANGE may be faster than PULL when returning ranges of values from linked workbooks. LINKEDRANGE may be slower than PULL
when returning single values or small ranges.

Furthermore, LINKEDRANGE can be used to link to named ranges that refer to a range of more than one cell. (PULL works with
named ranges that refer to a single-cell only.)

The VBA code and sample spreadsheets are located he
http://www3.sympatico.ca/sstackho/LinkedRange.zip

The .bas file can be used for easy importing into spreadsheets.

Although I have tested the code on a couple of machines, it certainly will not be as bulletproof as the PULL function. Harlan
has added several layers of armor to the PULL function over the years so that it works on more Excel versions and more
operating systems. Since I don't fully understand all of the error-checking logic in the PULL function, I wasn't comfortable
adding it to the LINKEDRANGE function. Harlan or anybody: please feel free to make this function better by adding any
additional logic as you see fit.

I will paste the code below, although it might not look very good with line-wrapping, etc. The code is also available at the
link above.

'-------------------------------------------------
'-------------------------------------------------

Option Explicit

Function LINKEDRANGE(Link As String) As Variant

' Developed by Shawn Stackhouse
' Inspired (and partially developed) by Harlan Grove and his PULL function
' that was in turn inspired by Bob Phillips and Laurent Longre
'-----------------------------------------------------------------
'This code is free software; you can redistribute it and/or modify
'it under the terms of the GNU General Public License as published
'by the Free Software Foundation; either version 2 of the License,
'or (at your option) any later version.
'-----------------------------------------------------------------

' Version History
'
' v1 - 2006-08-24
' v1.1 - 2006-08-25
' - changed structure to have the function accept a single input and split out the LINKREFERENCE logic to a separate
function
' - fixed problem with conflict between workbook-level and worksheet-level named ranges

' Purpose:
' This user-defined function can be used to get values from another spreadsheet, even if it is not open.
'
' This provides similar functionality to using regular Excel links, but allows the locations and names of
' linked workbooks to be specified via cells.
'
' LINKEDRANGE provides similar functionality to Harlan Grove's PULL function.
'
' LINKEDRANGE differs from Harlan Grove's PULL function in that LINKEDRANGE actually opens the linked workbook (in a
' separate Excel instance, since spreadsheets cannot be normally be opened in a UDF) to get the linked values.
' Furthermore, LINKEDRANGE can be used to link to named ranges that refer to more than one cell.
'
' LINKEDRANGE may be faster than PULL when returning ranges of values from linked workbooks. LINKEDRANGE may
' be slower than PULL when returning single values or small ranges.

' **** NOTE ****
' This function requires a full recalculation (Ctrl+Alt+F9) in order to update values

' Function Output:
' - a range of variable size
' - to return a range of cells, use LINKEDRANGE as a formula array (Ctrl+Shift+Enter)

Dim xlapp As Object, xlwb As Workbook, xlws As Worksheet
Dim r As Range, iChrPos As Long
Dim Directory As String, WorkbookName As String, WorksheetName As String, WorksheetRange As String
Dim NamedRangeRefersTo As String

On Error GoTo CleanUp

' Check to see if the referenced range is currently open in this Excel instance,
' by using an Evaluate function. If the function returns an error, then the
' range is not open (or the range is invalid).

' Do an EVALUATE on Link to see if the referenced range is currently open in this Excel instance.
' The Evaluate function will return an error if the range is not open
LINKEDRANGE = Evaluate(Link)

' If the range is not open (or invalid), an error will be returned from the above statement and
' the following section will be processed

If CStr(LINKEDRANGE) = CStr(CVErr(xlErrRef)) Then

' Let's decipher the Directory, WorkbookName, WorksheetName and WorksheetRange from the Link string.
' The Link string can be in a variety of formats.

' If the first character is not a single quote, then a Directory has not been defined.
If Left(Link, 1) "'" Then
Exit Function
End If

' Remove the leading single quote
Link = Mid(Link, 2, Len(Link) - 1)

' the Directory name will end at the last occurrence of ""
' find last occurrence of ""
iChrPos = InStrRev(Link, "")
Directory = Left(Link, iChrPos)
Link = Mid(Link, iChrPos + 1, Len(Link) - iChrPos)

' The next character will be a "[" unless the worksheet name has not been defined (and a workbook-level named range is
being used)
If Left(Link, 1) = "[" Then

' a worksheet is defined, the Workbook name will be until "]"
iChrPos = InStr(Link, "]")
WorkbookName = Mid(Link, 2, iChrPos - 2)
Link = Mid(Link, iChrPos + 1, Len(Link) - iChrPos)

' the worksheet name will be until a single quote
iChrPos = InStr(Link, "'")
WorksheetName = Mid(Link, 1, iChrPos - 1)
Link = Mid(Link, iChrPos + 2, Len(Link) - iChrPos)

Else

' a worksheet is not defined
WorksheetName = ""

' the workbook name will be until a single quote
iChrPos = InStr(Link, "'")
WorkbookName = Mid(Link, 1, iChrPos - 1)
Link = Mid(Link, iChrPos + 2, Len(Link) - iChrPos)

End If

' the WorksheetRange will be what is left over in the Link string
WorksheetRange = Link

' Create a new Excel instance
Set xlapp = CreateObject("Excel.Application")

' Open the linked workbook as read-only and do not update any links in the linked workbook.
' If the workbook doesn't exist, an error will be triggered.
Set xlwb = xlapp.Workbooks.Open(Directory & WorkbookName, UpdateLinks:=False, ReadOnly:=True)

' If a workbook-level name has been used (i.e. no WorksheetName was specified), then we need to refer
' to the RefersTo property of the named range to ascertain the proper worksheet.
If WorksheetName = "" Then

' temporarily add a blank worksheet to avoid problems with worksheet-level named ranges
Set xlws = xlwb.Worksheets.Add

NamedRangeRefersTo = xlwb.Names(WorksheetRange).RefersTo
' find the '!' in the range
iChrPos = InStr(1, NamedRangeRefersTo, "!")
WorksheetName = Mid(NamedRangeRefersTo, 2, iChrPos - 2)

'check for single quotes around WorksheetName
If Left(WorksheetName, 1) = "'" Then
WorksheetName = Mid(WorksheetName, 2, Len(WorksheetName) - 2)
End If
End If

' Refer to the WorksheetName worksheet.
' If the worksheet doesn't exist, an error will be triggered.
Set xlws = xlwb.Worksheets(WorksheetName)

' Refer to the WorksheetRange range on the worksheet.
' If the range doesn't exist or is invalid, an error will be triggered.
Set r = xlws.Range(WorksheetRange)

LINKEDRANGE = r

End If

CleanUp:
Set xlws = Nothing
If Not xlwb Is Nothing Then xlwb.Close 0
Set xlwb = Nothing
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End Function

'-------------------------------------------------
'-------------------------------------------------

' In order to assist with building the 'Link' parameter above (or 'xref' for PULL),
' I created another little UDF below that returns the Link parameter based on
' directory, workbook, worksheet and range inputs.

'-------------------------------------------------
'-------------------------------------------------

Function LINKREFERENCE(Directory As String, WorkbookName As String, WorksheetName As String, WorksheetRange As String) As
String

' This function can used as a helper for the LINKEDRANGE function. This function takes in information about the linked range
' and returns a link reference in the form needed by LINKEDRANGE.

' Function Inputs:
'
' Directory - the full path that contains the workbook from which values will be pulled
' - trailing "" is optional
' - e.g. C:LinkedData
' - relative directories can be used
' - e.g. "C:DummyDirectory..LinkedData" (evaluates to C:LinkedData)
'
' WorkbookName - the name of the workbook from which values will be pulled
' - e.g. LinkedWorkbook.xls
'
' WorksheetName - optional - leave blank if referring to a workbook-level named range
' - the name of the worksheet from which values will be pulled
' - e.g. LinkedSheet
'
' WorksheetRange - the cell range or named range from which values will be pulled
' - e.g. A1:E5
' - e.g. $A$1:$E$5
' - e.g. LinkedNamedRange

' Function Output:
' LINKEDRANGE - a string that contains the link reference in the form used by LINKEDRANGE

Dim sLinkReference As String

On Error GoTo CleanUp

' If the Directory, WorkbookName or WorksheetRange fields are not defined, then exit the function immediately.
If IsEmpty(Directory) Or IsEmpty(WorkbookName) Or IsEmpty(WorksheetRange) Then
Exit Function
End If

' Trim the inputs of any excess spaces
Directory = Trim(Directory)
WorkbookName = Trim(WorkbookName)
WorksheetName = Trim(WorksheetName)
WorksheetRange = Trim(WorksheetRange)

' check the Directory string and append a '' to its end if it doesn't already have one
If Right(Directory, 1) "" Then
Directory = Directory & ""
End If

' prefix with a single quote
sLinkReference = "'" & Directory ' e.g. 'C:LinkedData

' A worksheet does not need to be specified if using a workbook-level name.
' The format of the 'xref' statement to be evaluated differs if the workbook is not defined.
' Add a "[" if the worksheet name is defined.
If WorksheetName "" Then
sLinkReference = sLinkReference & "[" ' e.g. 'C:LinkedData[
End If

' Add the workbook name
sLinkReference = sLinkReference & WorkbookName ' e.g. 'C:LinkedData[LinkedWorkbook.xls

' Add a "]" if the worksheet name is defined
If WorksheetName "" Then
sLinkReference = sLinkReference & "]" ' e.g. 'C:LinkedData[LinkedWorkbook.xls]
End If

' Add the worksheet name (may be blank)
sLinkReference = sLinkReference & WorksheetName ' e.g. 'C:LinkedData[LinkedWorkbook.xls]LinkedSheet

' Add a single quote and exclamation point
sLinkReference = sLinkReference & "'!" ' e.g. 'C:LinkedData[LinkedWorkbook.xls]LinkedSheet'!

' Add the linked range (cell range or named range)
sLinkReference = sLinkReference & WorksheetRange ' e.g. 'C:LinkedData[LinkedWorkbook.xls]LinkedSheet'!A1:E5

LINKREFERENCE = sLinkReference

CleanUp:

End Function

'-------------------------------------------------
'-------------------------------------------------

' Finally, here's a small UDF to return the current workbook directory, which can be helpful
' for creating relative links. This is preferable to using the CELL("filename", A1) function
' since CELL("filename") is volatile (even though Microsoft claims it is not!)

'-------------------------------------------------
'-------------------------------------------------

Function ThisWorkbookDirectory() As String
' This function returns the directory of this workbook.

Dim sFullName As String
Dim iChrPos As Integer, iStrPos As Integer

sFullName = ThisWorkbook.FullName

' find last occurrence of ""
iChrPos = InStrRev(sFullName, "")
ThisWorkbookDirectory = Left(sFullName, iChrPos)

End Function

-------------------------------------------------

I hope that someone finds this helpful!

Thanks,
Shawn Stackhouse

--
----------------------------------------------
Posted with NewsLeecher v3.0 Final
* Binary Usenet Leeching Made Easy
* http://www.newsleecher.com/?usenet
----------------------------------------------

Following Roy's suggestion in another thread, I went to http://xcell05.free.fr/ and downloaded MoreFunc.xll. Here's what's cool: It has a six functions that handle regular expressions. Instead of complex formulas to extract the filename from

=CELL("filename")

You can use

=REGEX.MID( A1, CELL("filename"), "[^]+.xl[st]", 1, FALSE ) (find one or more characters not including "" or "[" ending in ".xls" or ".xlt")

I can't comment on speed, because I just got it -- in PERL and some other languages I know it can be very slow.

All links should open in a new window.

Free Add-ins (Sorted by name)
Language of site noted where applicable

PLEASE NOTE
07 July 2009: Downloads are currently unavailable for Colo's site. If you would be interested in testing the new HTML Maker designed specifically for the MrExcel forum, please see this thread
http://www.mrexcel.com/forum/showthread.php?t=399380

ASAP UtilitiesHomepage
Analysis Toolpack TranslatorDownload
Easy FilterHomepage
Function & Formula TranslatorDownload Currently Unavailable
Alternate Download site Currently Unavailable
Additional Information Currently Unavailable
Google SearchHomepage
HTML MakerDownload Currently Unavailable
FAQ
ktMsgBoxHomepage (English)
Homepage (***anese)
MOREFUNC.XLL
Homepage (French)
Download (English)
Alternate Download Site
MZ-Tools(for VB and VBA)Homepage
PULL FunctionDownload Currently unavailable
SendMailDownload
VB HTML MakerDownload
VB ScrollDownload
VBE ToolsHomepage
XY Chart LabelerDownload

Good day. I am using Excel 2002 on Win XP Pro. I have a worksheet that contains several columns of formulas relating to calculations against a value placed in Column A. I have just installed MoreFunc from Laurent Longre and will be using the INDIRECT.EXT function to replace lengthy references in the formulas. As such, I now need to Copy, or Auto-fill or Extrapolate the amended formula from Row 1 through Row 100 of several of the Columns in my worksheet. I have placed the $ where it needs to be in the formula so that my formulas will automatically increment where needed and hold the reference fixed were needed.

My problem is that I have also formatted the columns with borders that contain a solid line around the box and a broken line to separate the cells within the box. When I select the top Row of the Column and then attempt to copy the formula to Row 100, the border changes to the formatting of the top Row.

I seem to recall reading that there is a way to prevent this from happening. I have searched the message board and the Excel Tips book, but apparently I have not entered the correct phrases to search. Any help will be appreciated.

Thanks, Dan...

I'd be very grateful if someone can help with this - tearing my hair out!

I have a charting application "pushing" data record by record (row by row) directly into an Excel worksheet. There are no DDE statements involved in Excel.

I need to get a snapshot of calculations at specific points in time - when a new record comes in. Unfortunately, Excel keeps on re-calculating everything from scratch each time a new record/row comes in, overwriting previous results.

When a row is filled (say columns A-H) then calculations will be done (in column I) based on calculation on this row and other data held in other worksheets. However, this calculation in I (or a copy of it in another column) must remain permanent after it has done this first calculation as data in the other worksheets will change thereafter.

I've now spent ages trying to get this up and running without success. I have tried using RECALL but am finding it really difficult to get the right coding - IF it can be used with DDE updates rather than manual updates.

Would be really grateful of a reply from someone.

Thanks again.

Richard

Recall summary from http://www.rhdatasolutions.com/morefunc/#RECALL

RECALL

Returns the former contents of the caller cell (its contents before the last calculation).

SYNTAX :
=RECALL(Static)

- Static (boolean, optional) : if TRUE, the function is static (non-volatile). Default : FALSE.

If the formula of the caller uses or refers to a cell which uses a volatile function (RAND, INDIRECT, OFFSET...), this argument should not be set to TRUE.

EXAMPLES :

=RECALL()+1 : counts the number of recalculations in Excel.

=IF(A1RECALL(),A1,RECALL()) : returns the higher number which the cell A1 has ever contained.

=IF(MAX(A1:B10)RECALL(),MAX(A1:B10),RECALL()) : returns the higher number which the range A1:B10 has contained.

=RECALL(TRUE)+A1 : returns a cumulative sum of all values which have appeared in the cell A1.

=IF(COUNTA(A1)+1,RECALL(TRUE)+1) : counts how many times the contents of A1 have changed.

=IF(COUNTA(A1:B10),RECALL(TRUE)+1,0) : counts how many times the contents of any cell in A1:B10 has changed, or returns 0 if this range has been cleared.

=RECALL(TRUE)+(A2500) : counts how many times the value of A2 has been greater than 500.

Hey Biff,

Thanks for the reply.

And thanks for the very cool Add-In.

This looks like it will make my coding a lot easier.

-Minitman

On Mon, 17 Mar 2008 00:49:25 -0400, "T. Valko"
> wrote:

>The INDIRECT function *requires* that the referenced file(s) *MUST* be open.
>This is usually not desireable.
>
>A possible workaround is to download the *free* add-in, Morefunc.xll from
>this site:
>
>http://xcell05.free.fr/morefunc/english/index.htm
>
>It has a function called INDIRECT.EXT that works the same as the built-in
>INDIRECT *except* it will work on closed files.
>
>
>--
>Biff
>Microsoft Excel MVP

Dear technical wizards,

I desperately seek your guidance. I have a dashboard file (.xls) which references several, template excel files located on our Sharepoint (example: Template1, Template2, etc). Currently, I manually update the links in the Dashboard to each of the Template files. It is a hassle to say the least, and its limiting my ability to hand-over the dashboard to the user group.

I have successfully used Harlan Grove's "PULL" UDF to create a dynamic link to a closed file located on my local computer. However, it does not work with files on the Sharepoint.

Can you please advise if it is possible? If not, may I ask if you can suggest alternatives?

I truly appreciate your help.

Code I am using is below:

Thank you,
J.


	VB:
	
 
 
Function pull(xref As String) As Variant 
     'inspired by Bob Phillips and Laurent Longre
     'but written by Harlan Grove
     '-----------------------------------------------------------------
     'Copyright (c) 2003 Harlan Grove.
     '
     'This code is free software; you can redistribute it and/or modify
     'it under the terms of the GNU General Public License as published
     'by the Free Software Foundation; either version 2 of the License,
     'or (at your option) any later version.
     '-----------------------------------------------------------------
     '2005-05-02
     'fixed InStrRev syntax. Now using XL2K+ syntax.
     '-----------------------------------------------------------------
     '2005-04-18
     'added logic to check for date values from open workbooks, then
     'adjust for 1904 date system in source workbooks
     '-----------------------------------------------------------------
     '2004-05-30
     'still more fixes, this time to address apparent differences between
     'XL8/97 and later versions. Specifically, fixed the InStrRev call,
     'which is fubar in later versions and was using my own hacked version
     'under XL8/97 which was using the wrong argument syntax. Also either
     'XL8/97 didn't choke on CStr(pull) called when pull referred to an
     'array while later versions do, or I never tested the 2004-03-25 fix
     'against multiple cell references.
     '-----------------------------------------------------------------
     '2004-05-28
     'fixed the previous fix - replaced all instances of 'expr' with
     ''xref' also now checking for initial single quote in xref, and if
     'found advancing past it to get the full pathname [really dumb!]
     '-----------------------------------------------------------------
     '2004-03-25
     'revised to check if filename in xref exists - if it does, proceed;
     'otherwise, return a #REF! error immediately - this avoids Excel
     'displaying dialogs when the referenced file doesn't exist
     '-----------------------------------------------------------------
    Const DS1904DIFF As Long = 1461 
     
    Dim xlapp As Object, xlwb As Workbook 
    Dim b As String, r As Range, c As Range, n As Long, ds1904 As Boolean 
     
     '** begin 2004-05-30 changes **
     '** begin 2004-05-28 changes **
     '** begin 2004-03-25 changes **
     '** 2005-05-02 change - XL2K+ syntax **
    n = InStrRev((xref), "") 
     
    If n > 0 Then 
        If Mid(xref, n, 2) = "[" Then 
            b = Left(xref, n) 
            n = InStr(n + 2, xref, "]") - n - 2 
            If n > 0 Then b = b & Mid(xref, Len(b) + 2, n) 
             
        Else 
             '** 2005-05-02 change - XL2K+ syntax **
            n = InStrRev((xref), "!") 
            If n > 0 Then b = Left(xref, n - 1) 
             
        End If 
         
         '** key 2004-05-28 addition **
        If Left(b, 1) = "'" Then b = Mid(b, 2) 
         
        On Error Resume Next 
        If n > 0 Then If Dir(b) = "" Then n = 0 
        Err.Clear 
        On Error Goto 0 
         
    End If 
     
    If n

SEARCH THESE SITES AND MORE HERE

Microsoft Excel Add-in Links & Search Engine How to create an Excel Add-inBuild an Excel Add-inHow To Create Office COM Add-Ins by Using VBA and Office DeveloperFREE Excel Downloads Page with lots of freebies!Hey! That is Cool! Contributions from our Excel Help forum members.Calendar DownloadsDatabase Form, Navigating, Contact, Excel Web Toolbar and more!Excel Function DictionaryReferenceBrowserSpreadsheet ComposerEnhanced Data Form Access to source is not free.ExTools - Free Microsoft Excel add in download Stock market downloadsPop ToolsShared MacrosFUNCUSTOMIZE.DLL This free add-in allows to customize VBA user-defined functions in Excel's function wizard.MOREFUNC.XLL 65 add-in worksheet functions (translated from French into English). Examples.xlsAutoChart Manager This Excel add-in links the minimum and maximum values of a chart scales to worksheet cells.  You may also view the help file onlineChart gap for N/A This Excel add-in ensures that all line and scatter charts on the active page have a discontinuity wherever the data point contains a #N/A.Chart Image to Data This add-in is designed for those instances when one has an image of a chart but not the underlying data. Once the image is in electronic form, this add-in can help estimate the values of the associated data.Countdown TimerDigital ClockBalance Wiz & Fill DownCustom Radar Chart The software in the custom-radar.xls file allows one to create a radar chart that is more customized than the one that Excel creates by default.Directory List This add-in creates a listing of a directory and all its sub-directories. Gantt (Project) chart This add-in creates and maintains Gantt (project) charts.Gantt functions This document provides a downloadable workbook with two immediately usable functions related to creating a Gantt (or Gantt-style) chart.Hover Chart Label This Excel add-in shows data labels in a chart only when the mouse hovers over the associated data point (or the label itself).Interactive Chart This Excel add-in shows the Y or X value associated with the corresponding X or Y value for any XY Scatter chart.PivotChart Drilldown Written in response to various requests for an utility of this nature, the PivotChart Drill-down add-in does just what the name implies.PLOT manager This program lets one graph any function that can be created in an Excel worksheet.  In addition, the program improves the typical plot by concentrating its efforts on those portions of the graph with the greater curvature.SIMULATE SimRisk 2003, the successor to SIMULATE 2000, is a Monte Carlo simulation package written as an Excel add-in.Solve Polynomials This add-in solves for all solutions to polynomials of up to the 4th order (quadratic, cubic, and quartic or bi-quadratic).  The results (2 numbers or 3 or 4) can be all real or in the complex plane.Table of Contents This program lets one create a Table of Contents for an Excel workbook.  It uses information within the workbook and within specific worksheets to build the TOC.Text Write The Text Write Program is a Microsoft Excel workbook with a macro program that writes any sheet of any open workbook to a text file with lots of optionsTornado diagram This add-in creates Tornado diagrams that visually display the results of single-factor sensitivity analysis.Floating Browser This set of add-ins allow one to browse the 'Net without leaving the current application.  There is one add-in for each of Excel, PowerPoint, and Word.  Install the add-in at the appropriate location.Synchronized scrolling and Workbook windows navigator  A Excel-based calculator An interactive graph analyzer An enhanced find toolVBA Timer Are you looking for an easy way to add VBA capability that relies on timed events?VBA Calendar This add-in allows a developer to add a calendar to a userform with almost zero programming.Array FunctionsPage of Excel Add-ins (all in Italian)Getting Stock Prices into Excel (Yahoo Finance)XL Grade BookXL Manager Functions used to solve problems in engineering, finance, accounting, and many other disciplines.ChangeUnits Understands virtually any combination of units and automatically checks to make sure each conversion is valid.ASAP Utilities Over 300 additional functions for Excel.Worksheet ToolsHidden Sheets Add-inPivotTable HelperXL Name ManagerFlexFindUltimate Excel Add-inXLXtrFunMatrix & Linear algebra functionsXL Precision>Inspector TextXL NumbersVB Numerical Numbers>Simstool & FormlistXL StatisticsClassification Tree In ExcelStats Add-insSmart IndenterLoadPictureGDI.zipBIG List of Free DownloadsVBA Code CleanerXY Chart LabelerExcel UtilitiesCode DocumentorDialog ConverterFaceIDMZ-Tools>Google Search Add-inVBA Code CompareDelete Links> Excel ViewerExcess Format CleanerRemove Hidden DataSave as PDFSave as PDF or XPSSave As XPSTemplate Wizard with TrackingComment ControlComplete Excel NamesDuplicate MasterDifferenceEngineXEasyFilterExcelCalcsExcel ExplosionPOP ToolsPivot PlayPivot PowerQuick Date EntrySend MailRandom GeneratorXL Functions DictionaryEATBusiness FunctionsWebCab XL

SEARCH THESE SITES AND MORE HERE

The Site Below are in no Particular Order

Microsoft Excel MVP'sAndy Pope http://www.andypope.info/
Alan Beban http://home.pacbell.net/beban
Andrew Engwirda http://www.andrewsexceltips.com
Ashish Mathur http://www.ashishmathur.com
Bernd Held http://members.aol.com/Machero
Chip Pearson http://www.cpearson.com
Daniel Josserand http://dj.joss.free.fr/
David McRitchie http://www.mvps.org/dmcritchie/excel/excel.htm
Debra Dalgleish http://www.contextures.com/tiptech.html
Dick Kusleika http://www.dailydoseofexcel.com/
Ed Ferrero http://www.edferrero.com
Fernando Cinquegrani http://www.prodomosua.eu
Frank Isaacs http://www.vbapro.com
Jake Marx http://www.longhead.com/
Jan Karel Pieterse http://www.jkp-ads.com
John Lacher http://www.lacher.com
John Walkenbach http://www.j-walk.com/ss
Jon Peltier http://www.peltiertech.com/
Jorge Rodrigues http://exceler.blogspot.com
Ken Puls http://www.excelguru.ca
Laurent Longre http://xcell05.free.fr
Masaru Kaji http://puremis.net/excel/
Monika Weber http://www.jumper.ch
Nick Hodge http://www.nickhodge.co.uk
Orlando Magalhães Filho http://orlando.mvps.org
Patrick Molloy http://www.xl-expert.com
Rob Bovey http://www.appspro.com
Robert Rosenberg http://www.r-cor.com
Rodney Powell http://www.beyondtechnology.com
Ron de Bruin http://www.rondebruin.nl
Stephen Bullen http://www.oaltd.co.uk
Thomas Ramel http://users.quick-line.ch/ramel/
Ture Magnusson http://www.turedata.se
Tushar Mehta http://www.tushar-mehta.com
Zack Barresse http://www.vbaexpress.com

Hello,

If you want a generic solution for such string manipulations, you could try the
REGEX.MID function (available at http://xcell05.free.fr/english, Morefunc.xll
addin). It uses regular expressions to split the text.

Column A : =REGEX.MID(A1,".*d+")
Column B : =REGEX.MID(A1,"(?<!d)[^d]+$")

Cordially,

Laurent

carlito_1985 a écrit :
> Hi everyone,
> Im currently making a database by copying fields from another database,
> and I have some problems with separating numbers and letters.
> An example of what im trying to do is-
> Trying to split
>
> UNIT 7 5-9 WOON GARDENS
> 24 RYDER ROAD
> 6 JESTER COURT
> 11 GRADER ROAD
> 4 ALEN PLACE
> 7 WILENSON LANE
> UNIT 1 FAR ROAD
> SHOP 771 CANTON HWY
>
> So there are 2 different cells. One containing the writing upto and
> including the last number, and one with the remaining.
> For example
>
> Column A
> UNIT 7 5-9
> 24
> 6
> 11
> 4
> 7
> UNIT 1
> SHOP 771
>
> Column B
> WOON GARDENS
> RYDER ROAD
> JESTER COURT
> GRADER ROAD
> ALEN PLACE
> WILENSON LANE
> FAR ROAD
> CANTON HWY
>
> Is this actually possible? I tried text to columns, but that didn't
> really work too well. Any Ideas?
> Thanks,
> Andrew
>
>

I've created a workbook to calculate an incentive payment to be made to our staff. We're an accounting firm and pay a bonus of 10% of gross fees received for personal referrals, so long as "write-offs" are no more than 10% of fees.

The incentive is paid when the fees are recovered from the client, so often the quarter that the fees are invoiced in is different to the quarter the fees are recovered in. Each quarter new clients are added to the calculations, and I create a new worksheet each quarter to record that quarter's data.

At the end of all of this is a worksheet that sums all the data for each client across all the quarter worksheets to measure the overall fee/recoveries/write-off levels for each client.

At the moment I have very messy formulae that use the SUMIF command referring to each individual quarter. That is, SUMIF(quarter1) + SUMIF(quarter2) + SUMIF(quarter3) etc etc. Each new quarter I add another set of SUMIFs and it's getting very messy and difficult to read.

If each client's info was on the same ROW in each worksheet it would be easy as I wouldn't need to use SUMIF, but due to the fact that new clients are added each month and I use various sorting and SUB-TOTALS on the worksheets, the data is all over the place. SUMIF works beautifully within an individual worksheet, but cannot be used for 3-D ranges.

Is there something neat that I can use in its place....? HELP!!

Thanks!
MickyD.

UPDATE:

Came across http://www.mcgimpsey.com/excel/threedsumif.html and used the morefunc.xll add-in described there. It works fine.

Been playing with UDF's in .xll add-ins and found that these are indeed much
faster than VBA UDF's.
In a moderately complex math function I found it about 30 times faster.

Just one thing I don't quite understand:
I have the xll function in B1 and the VBA function in C1
Now I copy both down to row 65536.
All cells in column B are calculatated in about 1 second,
except cell B1, which is done last. This is done after the last cell in
column C is done.
Swapping the columns (XLL in C and VBA in B) makes no difference.

Any suggestions why this and if anything can be done about it?

RBS


No luck finding an answer? You could always try Google.