Paul Lockwood
Paul Lockwood

Reputation: 23

Set a range with a string/cell contents

I'm writing some code for a client which pulls data from many differently laid out files. I wanted to write something which was quite flexible for him in the future.

Therefore, he will be able to write for example y.offset(0,1) in a cell depending where in regards to the variable y the data will be.

The reason I haven't just made the the variable 1 is because it, and therefore the cell, may or may not include multiple & "blah blah"

Basically, I'm wondering if it's possible to write parts of code in a cell then pull them up and incorporate them into code.

For instance:

Dim y as range
Dim x as range
Dim c as string

Set Y = Sheet1.range("G4")
c = sheet1.range("A1")     [which contains the words y.offset(0,4)
Set x = c

This doesn't work, however I'm wondering if there's anything that can be done to get the same result.

Upvotes: 2

Views: 64

Answers (1)

user3598756
user3598756

Reputation: 29421

Your need is kind of a recursive and dangerous one

then it deserves such a recursive and dangerous answer

you could use the VBA Project Object Model (see here for info) and act as follows:

  1. Set your project to handle VBA Object Model

    follow all the steps you can see in the Introduction of the above given link to cpearson website Add reference to your project

    Disclaimer: please also read the CAUTION note in there

  2. add "helper" module

    add to your project a new Module and call it after "HelperModule" (you can call it as you like, but then be consistent with the chosen name)

    then add this code into this new module

    Function GetRange(refRng As Range) As Range
       Set GetRange = refRng 
    End Function
    
    
    Function SetToCellContent(refRng As Range, cellContent As String) As Range
        UpdateCodeModule cellContent
        Set SetToCellContent = HelpModule.GetRange(refRng)
    End Function
    
    Sub UpdateCodeModule(cellContent As String)
        Dim CodeMod As VBIDE.CodeModule
        Dim LineNum As Long
    
        Set CodeMod = ActiveWorkbook.VBProject.VBComponents("HelperModule").CodeModule
        LineNum = SearchCodeModuleLine(CodeMod, "Set GetRange")
        CodeMod.ReplaceLine LineNum, "    Set GetRange = " & cellContent
    End Sub
    
    Function SearchCodeModuleLine(CodeMod As VBIDE.CodeModule, FindWhat As String) As Long
        Dim SL As Long ' start line
        Dim SC As Long ' start column
        Dim EL As Long ' end line
        Dim EC As Long ' end column
        Dim Found As Boolean
    
        With CodeMod
            SL = 1
            EL = .CountOfLines
            SC = 1
            EC = 255
            Found = .Find(Target:=FindWhat, StartLine:=SL, StartColumn:=SC, EndLine:=EL, EndColumn:=EC, wholeword:=True, MatchCase:=False, patternsearch:=False)
        End With
    
        SearchCodeModuleLine = SL
    End Function
    
  3. Add this code to your main code

    Set x = SetToCellContent(y, c) '<--| call the function that will take care of updating code in 'GetRange()' function and returns a range relative to 'y' as per the "code" in 'c'
    

Upvotes: 1

Related Questions