How to use variable in excel macro editor

Question

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

cyboashu · Accepted Answer · 2014-09-07 09:27:39Z

1

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.

answered Sep 7, 2014 at 9:27

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Patrick Lepelletier Over a year ago

another pointer : when declaring variables like Dim LinkURL, PreStr, PostStr, NewURL As String , only the last will be a string , the others are by default declared as Variant if nothing is written.

Collectives™ on Stack Overflow

How to use variable in excel macro editor

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related