Replacing a variable in vba powerpoint

600 views Asked by At

I am trying to replace and save an integer in VBA Powerpoint.

I have a variable in 1 Subroutine named projectId. At the moment, the variable is set to 617. In another subroutine I am running a user input method that prompts the user for a new projectId and stores the new projectId to replace the "617". The only issue is the projectId go to 0 when I close the PowerPoint and reopen it.

Is there any way to physically replace the code projectId = 617 with projectId = 699 for this example.

Below is the code Im working with, notice that projId is a global veriable:

    Dim projId As Integer

Sub ChangeProjID()
    Dim strProjId As String

    Dim intProjId As Integer

    strProjId = InputBox("Enter the Project ID given by example:", "Project ID Input")

    On Error GoTo NotValidID
    intProjId = CInt(strProjId)
    projId = intProjId
    MsgBox " Your new project ID will be set to " & intProjId & " until changed again"

    Exit Sub

NotValidID:

    MsgBox " You must enter a valid integer"

    End Sub

Public Sub CommentConnect(control As IRibbonControl)


    Dim URL As String
    projId = 617
    URL = "example.com/prID="


    ActivePresentation.FollowHyperlink (URL & projId)

End Sub
2

There are 2 answers

2
Steve Rindsberg On BEST ANSWER

FreeMan's got the right idea ... store the value in non-volatile memory.

Luckily, PowerPoint has a feature that's perfect for this: Tags.

With ActivePresentation
  .Tags.Add "ProjectID", Cstr(intProjID)
End With

The string version of intProjID is now a permanent "tag" attached to the presentation object. To retrieve it:

MsgBox ActivePresentation.Tags("ProjectID")

Each presentation, slide and shape can have pretty much as many of these tags as you like.

2
FreeMan On

If CommentConnect() is called after ChangeProjID(), the line projId = 617 will override any value the user entered in ChangeProjID().

I see a couple of options for fixing this.

  1. Change the signature of ChangeProjID() to:
    Function ChangeProjID() as Integer
    then change the line in CommentConnect to:
    projId = ChangeProjID()
  2. Change the line in CommentConnect() to:
    If projId = 0 then
      projId = 617 'use this value as a default
    End If

For option 1, the complete, changed code would look like:

Function ChangeProjID() As Integer
    Dim strProjId As String
    Dim intProjId As Integer

    strProjId = InputBox("Enter the Project ID given by example:", "Project ID Input")

    On Error GoTo NotValidID
    intProjId = CInt(strProjId)
    MsgBox " Your new project ID will be set to " & intProjId & " until changed again"
    ChangeProjID = intProjId

    Exit Sub

NotValidID:
    MsgBox " You must enter a valid integer"
End Sub

Public Sub CommentConnect(control As IRibbonControl)
    Dim URL As String
    projId = ChangeProjID
    URL = "example.com/prID="
    ActivePresentation.FollowHyperlink (URL & projId)
End Sub

The complete, changed code for option 2 would be:

Public Sub CommentConnect(control As IRibbonControl)


    Dim URL As String
    if projId = 0 then
      projId = 617           'use project 617 as the default
    End If
    URL = "example.com/prID="


    ActivePresentation.FollowHyperlink (URL & projId)

End Sub

Note that whatever you do, projId will default to 0 when you first open the PPTX - that's the nature of variables, they have a default value when the application starts until set otherwise.

If you want, you can create some sort of mechanism for storing the projId in non-volatile memory (a text file, the registry, a hidden (background color = foreground color) field on the first slide of the PPT, etc). Or you can have some sort of initialization for projId so that it always gets set to some known, non-zero value when your code is launched, but it will not retain its last set value all by itself.