logo
down
shadow

Loop Though All UDF Names in Project


Loop Though All UDF Names in Project

By : Brian Russell
Date : November 22 2020, 10:31 AM
To fix this issue This question: Searching for function usage in Excel VBA got me thinking about a process for automating a search for all UDFs being used in a spreadsheet. Something along the lines of:
code :
Option Explicit

' Add reference to Microsoft Visual Basic for Applications Extensibility 5.3 Library

Public Sub FindFunctionUsage()
    Dim udfs
    udfs = ListProcedures("Module1")
    If Not IsArray(udfs) Then _
        Exit Sub

    Dim udf
    Dim findResult

    For Each udf In udfs
        Set findResult = Cells.Find(What:="=" & udf, After:=Cells(1), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False)

        If Not findResult Is Nothing Then _
            MsgBox udf & " is in use"
    Next udf
End Sub

' Source for ListProcedures : http://www.cpearson.com/excel/vbe.aspx
Private Function ListProcedures(moduleName As String)
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim LineNum As Long
        Dim NumLines As Long
        Dim WS As Worksheet
        Dim rng As Range
        Dim ProcName As String
        Dim ProcKind As VBIDE.vbext_ProcKind

        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents(moduleName)
        Set CodeMod = VBComp.CodeModule

        Dim result
        With CodeMod
            LineNum = .CountOfDeclarationLines + 1
            Do Until LineNum >= .CountOfLines
                ProcName = .ProcOfLine(LineNum, ProcKind)
                If ProcKindString(ProcKind) = "Sub Or Function" Then
                    If IsArray(result) Then
                        ReDim Preserve result(LBound(result) To UBound(result) + 1)
                    Else
                        ReDim result(0 To 0)
                    End If
                    result(UBound(result)) = ProcName
                End If

                LineNum = .ProcStartLine(ProcName, ProcKind) + _
                        .ProcCountLines(ProcName, ProcKind) + 1
            Loop
        End With
        ListProcedures = result
    End Function

    Function ProcKindString(ProcKind As VBIDE.vbext_ProcKind) As String
        Select Case ProcKind
            Case vbext_pk_Get
                ProcKindString = "Property Get"
            Case vbext_pk_Let
                ProcKindString = "Property Let"
            Case vbext_pk_Set
                ProcKindString = "Property Set"
            Case vbext_pk_Proc
                ProcKindString = "Sub Or Function"
            Case Else
                ProcKindString = "Unknown Type: " & CStr(ProcKind)
        End Select
    End Function

' Content of Module1
Public Sub Sub1()

End Sub

Public Function Func1(ByRef x As Range)

End Function

Public Sub Sub2()

End Sub


Share : facebook icon twitter icon
Passing strings as variables names in R for loop, but keeping names in results

Passing strings as variables names in R for loop, but keeping names in results


By : Gertea
Date : March 29 2020, 07:55 AM
Hope that helps You could do this (only send the outcome and predictor columns one at a time to glm):
code :
for (i in 1:length(dumber)) { 
  print(summary(glm(WorldSeries ~ . , data=baseball[, c("WorldSeries",  dumber[i])], 
                                       family=binomial)))
} 
for (i in 1:length(dumber)) { print( paste0("Current predictor is ...", dumber))
  print(summary(glm(WorldSeries ~ get(dumber) , data=baseball, family=binomial)))
} 
C# Loop through List of names and display names on a different label each time it loops

C# Loop through List of names and display names on a different label each time it loops


By : user2318901
Date : March 29 2020, 07:55 AM
To fix this issue Use a While instead of a For
Put your labels on the same control (ex. Panel1)
code :
List<string> labels = new List<string>() { "lblFirstName", "lblSecondName" , "lblThirdName" , .......... };
while (names.Count > 0) {
    Random rand = new Random();
    int index = rand.Next(names.Count);
    var name = names[index];
    names.RemoveAt(index);

    index = rand.Next(labels.Count);
    //WebForms
    Panel1.FindControl(labels[index]).Text = name;
    Panel1.FindControl(labels[index]).Visible = true;

    //WinForms
    Panel1.Controls(labels[index]).Text = name;
    Panel1.Controls(labels[index]).Visible = true;

    labels.RemoveAt(index);
}
How in Eclipse/Gradle/Java project one can have different names for a project name and a root project directory?

How in Eclipse/Gradle/Java project one can have different names for a project name and a root project directory?


By : frodriguezu
Date : March 29 2020, 07:55 AM
wish help you to fix your issue If you don't explicitly set the root project name in settings.gradle, the project name will default to the folder name If you don't explicitly set the eclipse project name in build.gradle, the eclipse project name will default to the Gradle project name
Eg: settings.gradle
code :
rootProject.name = 'foo'
apply plugin: 'eclipse' 
eclipse.project.name = 'bar' 
For Each ... Next loop is finding names in Word doc, then input the names into Excel cells but without THE first name

For Each ... Next loop is finding names in Word doc, then input the names into Excel cells but without THE first name


By : João Henrique
Date : March 29 2020, 07:55 AM
hop of those help? @michalroesler The code below will search a range in a Word document and return a scripting.dictionary of names where the name conforms to the rules 1. the number is at the start of a line 2. the name is terminated by a comma.
This appears to be what is used in your document.
code :
Public Sub FindNamesByRonRosenfeldWithInput()

    Const FirstName                                     As Long = 1

    'It would probably be better to padd TextToFInd1 & 2 as parameters to this function
    'Dim TextToFind1 As String
    Const TextToFind1                                   As String = "REGON 364061169, NIP 951-24-09-783,"

    'Dim TextToFind2 As String
    Const TextToFind2                                   As String = "- ad."

    Dim WordApp As Word.Application
    Set WordApp = GetObject(, "Word.Application")

    Dim WordDoc As Word.Document
    Set WordDoc = WordApp.ActiveDocument

    'Dim Rng As Word.Range - used more expressive name
    'Set Rng = WordApp.ActiveDocument.Content
    Dim myNamesSearchRangeInWord As Word.Range
    Set myNamesSearchRangeInWord = getSearchRangeInDocument(WordDoc, TextToFind1, TextToFind2)
    myNamesSearchRangeInWord.Select
    'If startPos = 0 Or endPos = 0 Then
    If myNamesSearchRangeInWord Is Nothing Then

        '@Ignore FunctionReturnValueDiscarded
        MsgBox ("Client's names were not found!")
        Exit Sub

    End If

    Dim myNames As Scripting.Dictionary
    Set myNames = GetNumberedNames(myNamesSearchRangeInWord)
    '@Ignore VariableNotUsed
    Dim myNamesStr As Variant
    myNamesStr = myNames.Items
    'Delete the first name that we found from the list of names
    myNames.Remove FirstName
    myNamesStr = myNames.Items
    'Dim firstName As String
    'Dim fullName As Word.Range

    ' The fourth line below shows you are running in Excel so the next two lines are not needed
    ' because you already have the excel application object.
    ' Dim ExcelApp As Excel.Application
    ' Set ExcelApp = GetObject(, "Excel.Application")
    Dim mySheet As Excel.Worksheet
    Set mySheet = Application.ActiveWorkbook.ActiveSheet
    'Dim Para As Word.Paragraph

    'Dim pStart As Long
    'Dim pEnd As Long
    'Dim Length As Long
    'Dim startPos As Long
    'Dim endPos As Long
    'Dim x As Long

    Application.ScreenUpdating = False

    'Assigning object variables
    'x = 11
    'InStr function returns a Variant (Long) specifying the position of the first occurrence of one string within another.
    'startPos = InStr(1, Rng, TextToFind1) - 1    'here we get 1421, we're looking 4 "TextToFind1"
    'endPos = InStr(1, Rng, TextToFind2) - 1      'here we get 2497, we're looking 4 "- ad."
    'If startPos = 0 Or endPos = 0 Then Exit Sub
    'Rng.SetRange Start:=startPos, End:=endPos
    'Debug.Print Rng.Paragraphs.Count


    'Else
        'The full name of the first counterparty found, is not supposed to be written into the worksheet.
        'It's not important and I just want to skip it.
        'Macro needs to start entering names from the full name of the second counterparty that's found,
        'in a way that: 2nd counterparty's full name is written into cell "A12", 3rd counterparty's full name is written into cell "A13",
        'and 4th counterparty's full name is written into cell "A14" and so on.

'        For Each Para In Rng.Paragraphs
'            firstName = Trim$(Para.Range.Words(3))
'            'Debug.Print firstName
'            pStart = InStr(Para, ".") + 1       'here we get 3
'            Length = InStr(Para, ",") - pStart  'here we get 14/25/39 - 3
'            Debug.Print Trim(Mid(Para, pStart, Length))
'            x = x + 1
'            Cells(x, 1).Value = Trim(Mid(Para, pStart, Length))
'        Next Para
'    End If

    'transfer the remaining names to the excel worksheet in a single action
    Dim myitems As Variant
    myitems = myNames.Items
    Dim myXlRange As Excel.Range
    Set myXlRange = mySheet.Range("A13")
    Set myXlRange = myXlRange.Resize(myNames.Count, 1)
    myXlRange.Value = Application.WorksheetFunction.Transpose(myNames.Items)

End Sub


Public Function getSearchRangeInDocument(ByVal ipDoc As Word.Document, ByVal ipStartText As String, ByVal ipEndText As String) As Word.Range

    With ipDoc.StoryRanges.Item(wdMainTextStory)

        With .Find

            .ClearFormatting
            .Text = ipStartText
            .Replacement.Text = vbNullString
            .MatchWildcards = False
            .Wrap = wdFindStop

            If Not .Execute(Replace:=wdReplaceNone) Then Exit Function

        End With

        Dim mySearchRange As Word.Range
        Set mySearchRange = .Duplicate
        .Collapse Direction:=wdCollapseEnd
        '@Ignore FunctionReturnValueDiscarded
        .MoveStart Count:=1

        If Not .Find.Execute(findtext:=ipEndText) Then Exit Function

        mySearchRange.End = .End

    End With

    Set getSearchRangeInDocument = mySearchRange

End Function


Public Function GetNumberedNames(ByVal ipRange As Word.Range) As Scripting.Dictionary

    Dim myEndOfSearchRange As Long
    myEndOfSearchRange = ipRange.Document.StoryRanges.Item(wdMainTextStory).End
    Dim myNames As Scripting.Dictionary
    Set myNames = New Scripting.Dictionary

    With ipRange

        With .Find

            .ClearFormatting
            .Text = "(<)([0-9]{1,})(.)( {1,})([!,]{1,})"
            .MatchWildcards = True
            .Wrap = wdFindStop

        End With

        Do While .Find.Execute

            ' If it is likely that the loop will take a long time then put a DoEvents Statement here
            myNames.Add getNumberFromRange(.Duplicate), getNameFromRange(.Duplicate)
            .Collapse Direction:=wdCollapseEnd
            '@Ignore FunctionReturnValueDiscarded
            .MoveStart Count:=1
            .End = myEndOfSearchRange

        Loop

    End With

    Set GetNumberedNames = myNames

End Function


Public Function getNumberFromRange(ByVal ipRange As Word.Range) As Long

    '@Ignore FunctionReturnValueDiscarded
    ipRange.MoveStartUntil cset:="0123456789"
    '@Ignore FunctionReturnValueDiscarded
    ipRange.MoveEndUntil cset:=".", Count:=wdBackward
    '@Ignore FunctionReturnValueDiscarded
    ipRange.MoveEnd Count:=-1
    getNumberFromRange = CLng(Trim$(ipRange.Text))

End Function


Public Function getNameFromRange(ByVal ipRange As Word.Range) As String

    '@Ignore FunctionReturnValueDiscarded
    ipRange.MoveStartUntil cset:="ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    getNameFromRange = Trim$(ipRange.Text)

End Function
r-project SixSigma ss.rr gives Error in `row.names<-.data.frame`(`*tmp*`, value = value) : invalid 'row.names' length

r-project SixSigma ss.rr gives Error in `row.names<-.data.frame`(`*tmp*`, value = value) : invalid 'row.names' length


By : Araz Arif
Date : March 29 2020, 07:55 AM
Hope that helps Gage R&R needs repeated measures in order to assess the Measurement System. Apparently, the data set contains only one observation for each combination of factors (if any). For example, the ss.data.rr data.frame has 3 measurements of each prototype for each operator:
Related Posts Related Posts :
  • How to restrict VBA code to a specific worksheet?
  • How to insert column name in the destination table in ssis?
  • Insert text into the background of a cell
  • how do I generate random timestamps in Excel
  • VBScript Opening folder to convert csv to xls
  • Creating a VBA Refresh Macro in Smart View for Oracle
  • Excel Performance issues with countif on multiple rows
  • excel vba http request download data from yahoo finance
  • Excel Vba Loop , not running properly
  • Efficiently hiding / unhiding many (+500) rows in Excel VBA
  • Excel's Find function; Exceptions over values
  • reading data from an excel sheet in perl
  • Using Webservice function in Excel to pull "Next Earnings Date" from Yahoo Finance using Yahoo API
  • Three And (&)'s into an if statement VBA for Excel
  • Certain fractions being calculated in excel 2013
  • Excel formula to find sum of difference
  • countfis or sum if array to work out if given time is between the value of two columns
  • Error in Date Cell in CSV
  • Syntax while trying to include The multi-part identifier [°C] in command text
  • How to find day of the week from known date and month but variable year
  • Modify Marker Transparency with Excel VBA
  • Excel tricky formula
  • Searching for function usage in Excel VBA
  • Convert from list to flat CSV with modified data
  • Count how many words from a list appear in a cell
  • Insert Missing Years between 2 years
  • Creating hierarchical sequence in Excel using several functions in formula
  • On Error GoTo statement is still executing although there is no error generated
  • Find last digits with IF
  • Use Excel VBA to find column matches & merge based on the values of two other column
  • PowerPivot not sending Authorization header in Basic Authentication to OData Svc
  • Vlookup not working with text search
  • Paste value from a Userform to a Excel Sheet
  • Excel 2010 Macro to compare two columns for finding the matched value
  • #VALUE error when dealing with long string in UDF in VBA(excel)
  • Refer to a cell in another worksheet by referencing the current sheet's name
  • VBA Excel 2007 : Need to loop copy and loop count number except zero every row above
  • Excel: Conditional probabilities of winning a tiebreaker game
  • How to find groups of individuals who attended multiple events together?
  • Using DateTime when referencing a cell with SUMIFS()
  • VBA Copy & Transpose Data Range
  • Huge excel file... any advice?
  • Looped macro to create a new sheet, rename it, add data from the web, then loop back through until complete
  • copy paste of tab delimited text results in strange formatting in Excel?
  • Excel user defined function: Nth_Occurence
  • Excel ActiveX Button is clickable, but does not work
  • Array Formula Conditional Count
  • YEAR() fitting to WEEKNUM(...,21) in Excel
  • Macro that follows a link and downloads the table into a new sheet
  • How to Concatenate multiple columns if not empty
  • Excel - List all the combinations of two words from one list, in two other columns
  • How do I do my Pentaho Report take the Excel datasource file along to BA/BI Server?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org