CODEWITHSUNDEEP
excelvbasas
user2337871
user2337871

Reputation: 462

VBA Populate Different Objects Using the Value from a Sub Argument

Good Afternoon All,

Let me preface this post by saying I have very little experience using VBA, but it is the tool I have to work with in this instance, so here I am. I am using the SAS Add-In for Microsoft Office, which isn't entirely relevant in this situation as far as I can tell, but it is best to give you some context. I have 2 subs that function very differently but make use of the same named ranges.

The first sub uses values from 4 cells within Excel and submits them to a stored process which returns values to particular cells which are defined as named ranges - approximately 64. Once that takes place the end-user will validate the results, make some changes to the values in the 64 cells and then submit the second sub. The second sub then passes the values contained within the 64 cells for processing by a second stored process.

It makes more sense to me to have the 64 variables defined once and not multiple times to save on maintenance, but they are applied to different objects for example:

Sub1

Dim outputParams As SASRanges
Set outputParams = New SASRanges

Dim DD_BD_Age As Range
Set DD_BD_Age = Sheet1.Range("DD_BD_Age")

outputParams.Add "DD_BD_AGE", DD_BD_Age

Sub2

Dim prompts As SASPrompts
Set prompts = sas.CreateSASPromptsObject

Dim DD_BD_Age As Range
Set DD_BD_Age = Sheet1.Range("DD_BD_Age")

prompts.Add "DD_BD_AGE", DD_BD_Age

Is there any way that I can define these variables for use across either sub. If I was using SAS I would create a macro with a parameter which would allow me to specify the value of outputParams or prompts depending on the context in which I was using them.

Admittedly, with my limited experience with VBA, I may just be making things more difficult than they need to be, so please let me know if this is the case.

Any help would be greatly appreciated.

Upvotes: 0

Views: 173

Answers (1)

Tim Williams
Tim Williams

Reputation: 166735

If your SAS objects have a common "Add" method then you can do something like this:

Dim outputParams As SASRanges
Set outputParams = New SASRanges
AddParams outputParams 


Dim prompts As SASPrompts
Set prompts = sas.CreateSASPromptsObject
AddParams prompts


'add common parameters
Sub AddParams(obj As Object)
    With obj
        .Add "DD_BD_AGE", Sheet1.Range("DD_BD_Age")
        'etc for the rest
    End With
End Sub

Upvotes: 1

Related Questions