CODEWITHSUNDEEP
excelvba
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