CODEWITHSUNDEEP
excelvba
Dan
Dan

Reputation: 1033

How to use variable in excel macro editor

I'm new to Excel programming and am making my first program. I'm having trouble with using a variable in a call function (I think they are called functions!), the trouble is that I don't think the variable is being expanded in my function, could someone please help me to resolve it.

Sub HyperLinkReplace()

    Dim MyPath As String
    MyPath = Environ("UserProfile") & "\Games\"

    'MsgBox "MyPath: value is " & MyPath

    Call ReplaceHyperlinkURL("C:\Users\Kids\Games\", "& MyPath &")

End Sub

Public Sub ReplaceHyperlinkURL(FindString As String, ReplaceString As String)
    Dim LinkURL, PreStr, PostStr, NewURL As String
    Dim FindPos, ReplaceLen, URLLen As Integer
    Dim MyDoc As Worksheet
    Dim MyCell As Range
    On Error GoTo ErrHandler

    Set MyDoc = ActiveSheet
    For Each MyCell In MyDoc.UsedRange
    If MyCell.Hyperlinks.Count > 0 Then
        LinkURL = MyCell(1).Hyperlinks(1).Address
        FindPos = InStr(1, LinkURL, FindString)
        If FindPos > 0 Then 'If FindString is found
            ReplaceLen = Len(FindString)
            URLLen = Len(LinkURL)
            PreStr = Mid(LinkURL, 1, FindPos - 1)
            PostStr = Mid(LinkURL, FindPos + ReplaceLen, URLLen)
            NewURL = PreStr & ReplaceString & PostStr
            MyCell(1).Hyperlinks(1).Address = NewURL 'Change the URL
        End If
    End If
    Next MyCell
Exit Sub
ErrHandler:
MsgBox ("ReplaceHyperlinkURL error")
End Sub

What am I doing wrong in the above?

Thanks

Upvotes: 0

Views: 97

Answers (1)

cyboashu
cyboashu

Reputation: 10443

Change Call ReplaceHyperlinkURL("C:\Users\Kids\Games\", "& MyPath &") to Call ReplaceHyperlinkURL("C:\Users\Kids\Games\", MyPath)

MyPath is already a variable, enclosing that in quotes makes it a string literal.

Upvotes: 1

Related Questions