Solved: Is it possible for a function to affect another cell?

[Sock]

Hey everyone, I just registered here. I'm really new to VBA (I only know very simple things... I have had a hard time finding guides to learning this).

I'm just wondering if I can create a function that can affect other cells. For example...

Background: Someone has a big model they've created with multiple inputs and they want to build a big scenario table on these inputs. Using Data Tables will only let them evaluate (at most) two variables. They feel their only option is to change the variables manually and record the final output on its own.

I can think of a way to do this without creating a function, but I think it would be pretty elegant for the user to just run the operation with a function.

The only way I can think of a function being able to accomplish this, however, is for it to be the one to change the input values and record the output value?

I hope this question makes sense. I will try to whip up an example spreadsheet if you guys want to see what I would be trying to accomplish.

Thanks so much and I look forward to being a part of this community! :)

[Simon Lloyd]

Welcome to the forums :), have you tried using pivottables?

[Sock]

My only problem with using pivot tables is I only have a singular output value, but with multiple input values. The output is a sum of values that are dependent on the inputs. I know of using Scenario Manager, however I've found this can be destructive (especially if in the wrong hands) and can be tedious to build it out. Do you know of another way? With all that said...

Waaiiiit wait wait wait wait.

A function isn't what I want at all! Hahaha!

However, I do have a follow-up question that's somewhat related. How do you create a dynamic multidimensional array? I don't mean in the sense that we use ReDim and such to affect the size of a 2D, 3D, 4D, array, etc. I mean having an array that changes its dimensions depending on the situation. Ie. something like:

SuperDimension()

Turns to

SuperDimension(0,0)

Turns to

SuperDimension(3,4,2,5,3,2)

etc. etc. etc.

The dimensions would vary depending on the number of arrays chosen by the user.

Put another way, is there a way to tell VBA to create a multidimensional array that adds dimensions equal to the selection of the user?

[Paul_Hossler]

couple of thoughts tha might help

neither is extremely elegant, and I took a guess at how you'd have the user select the number of dimensions and the upper limit and base (0 or 1)





Option Explicit
Dim A() As Variant
Sub One()
Call SuperDim1(A, 3)
End Sub
Sub SuperDim1(ByRef aSuperArray() As Variant, Optional DefaultDims As Long = 2)
Dim iNumDims As Long


iNumDims = Application.InputBox("How dimensions would you like?", "Num Dims", DefaultDims)

If iNumDims < 0 Or iNumDims > 5 Then Exit Sub

Select Case iNumDims
Case 1
ReDim aSuperArray(1 To 100)
Case 2
ReDim aSuperArray(1 To 100, 1 To 100)
Case 3
ReDim aSuperArray(1 To 100, 1 To 100, 1 To 100)
Case 4
ReDim aSuperArray(1 To 100, 1 To 100, 1 To 100, 1 To 100)
Case 5
ReDim aSuperArray(1 To 100, 1 To 100, 1 To 100, 1 To 100, 1 To 100)
End Select

End Sub
Sub Two()
Call SuperDim2(A, 2, 3, 4)
Call SuperDim2(A, 6, 7)
Call SuperDim2(A, 7, 11, 13, 17, 19)
Call SuperDim2(A, 7, 11, 13, 17, 19, 23, 29)
With Worksheets("Sheet1")
Call SuperDim2(A, .Range("a1"), .Range("b1"), .Range("c1"), .Range("d1"))
End With
End Sub
Sub SuperDim2(ByRef aSuperArray() As Variant, ParamArray DimLimits() As Variant)
Dim i As Long

'ParamArray goes from 0 to n-1
If UBound(DimLimits) + 1 > 5 Then Exit Sub

Select Case UBound(DimLimits) + 1
Case 1
ReDim aSuperArray(1 To DimLimits(0))
Case 2
ReDim aSuperArray(1 To DimLimits(0), 1 To DimLimits(1))
Case 3
ReDim aSuperArray(1 To DimLimits(0), 1 To DimLimits(1), 1 To DimLimits(2))
Case 4
ReDim aSuperArray(1 To DimLimits(0), 1 To DimLimits(1), 1 To DimLimits(2), 1 To DimLimits(3))
Case 5
ReDim aSuperArray(1 To DimLimits(0), 1 To DimLimits(1), 1 To DimLimits(2), 1 To DimLimits(3), 1 To DimLimits(4))
End Select

End Sub


I stopped at 5D, but you can easily expand / decrease that assumption

Paul

[SamT]

Fill A1:C1 with numbers, Formula in D1 =MyFunc
Function MyFunc(Input1 As Range, Input2 As Range, Input3 As Range)
Dim Y
Y = Input1.Offset(0, 1)
MyFunc = Y * Input3
End Function

[Sock]

Best. Forum. Ever!!

Thank you so much, guys. This is all incredible!

How did you all build these skills and knowledge?

[Sock]

I apologize for the double post and yet another set of questions.

NOTE: If the text of this post is too long, feel free to just skip to the code I've posted along with my question.



I'm trying to create an array from a selection of cells. These cells may move around, change, etc. according to the user, which is why I need to designate the selected cells as the value for the array (ie. explicitly specifying "A4:G4" is not going to work).

I'm not sure how to do this properly, as everywhere else I've checked similar questions, the response has usually been "you don't have to select the values you want, you can just specify the range itself in the spreadsheet", which isn't going to suit my needs.

What I've tried doing is counting the number of cells in the range and looping over that counted amount to fill the array with the selected values. However, I'm running into a weird error, so to simplify it, I've chosen a single row example with five values I'm trying to fit into an array to narrow down the error I get (which is error 438).

Below is the code snippet. I can't for the life of me figure out why I get error 438: Object doesn't support this property or method. What have I done wrong?


Option Explicit
Sub ArrayAdderSnip()

Dim ArrayAdder(0, 4) As Variant 'I've made it (0,4) for simplicity. This is a common type of range to be selected
Dim SimpleFiller As Range
Dim ColIndex As Integer

Set SimpleFiller = Excel.Application.InputBox("Select left-most Value", "Locator", , , , , , 8)

For ColIndex = 0 To ColIndex = 4
ArrayAdder(0, ColIndex) = SimpleFiller.Offest(0, ColIndex).Value 'Error 438 occurs here

Next ColIndex

End Sub

[Paul_Hossler]

Typo



.Offset


Paul

[SamT]

selection of cells. These cells may move around, change, etc. according to the user, which is why I need to designate the selected cells

Why do you think that an Array is the only, or even the best, way to do this?

As I see it, (from my house,) your situation with us is: You have a problem. You think you have the answer. You ask us for help with the answer.

Why not ask for help with the problem?

[Sock]

Typo



.Offset


Paul

Well that's embarrassing. Thanks! LOL


Why do you think that an Array is the only, or even the best, way to do this?

As I see it, (from my house,) your situation with us is: You have a problem. You think you have the answer. You ask us for help with the answer.

Why not ask for help with the problem?

I'm still really new to the whole thing, and I don't want to come to you guys saying "hey, write my code for me", which is how it would seem to me if I came from a different point. I feel like that would be disrespectful to you. So instead I'm just trying to use the knowledge I have and to work through it. I apologize if I came off the wrong way.

I'm sure it's not the only nor the best way to solve the problem. Rather, it's the only way I can think of (again, I'm very very new to VBA). With that said, if a user intends to select a set of values to be placed into a single selected cell (one after the other) what might be some other ways to accomplish this? Thank you so much for your time. :bow: :D

[SamT]

I don't want to come to you guys saying "hey, write my code for me"We appreciate that. Really.

We also like, "Hey, Help me learn how to write code that will do 'this'."

First, a few things you must know:
All the bold words in this post are accessible through VBA's Help system by Placing the cursor (in VBA,) Inside the word and pressing F1.
Make sure that in the VBA Menu>>Tools>>Options>>Editor tab that everything in the Code Settings box is checked. I like 2 for the Auto Indent option. YMMV. I also check the bottom two options in the Window Settings group.
VBA treats Excel like a programming Object
The main part of the object model, in descending order are:
Application, (Excel)
Workbook
Worksheet
Range
Objects have Properties, Methods, and Events
All Procedures (Macros) must be triggered before they will run.
In coding, the language rules of Spelling, Grammar, and Punctuation are strictly enforced. By failure.
You will make many changes to your code before you're satisfied with it.I regularly use the word "Workbook," to get to Help, then I will use the Contents section to look up what I want.


It sounds to me as if you want your macro triggered by the User. This could be by using the Excel Menu Tools >>Macros, or you can put a Command Button on the sheet with Menu>>View>>Toolbars>>Controls or ...>>Toolbars>>Forms. There are also automatic ways using Object Events.


That is the first thing to think of when designing your code, but it is near the end of writing the code before you implement it. While working on your code, you will usually press F8 to step through it looking for problems or by pressing F5 to run it so it can call other bits of code.


I'm getting there :yes


a user intends to select a set of values to be placed into a single selected cell (one after the other)
The User has to make two selections. See Below.

"(one after the other)" Say what?:dunno

There are several possibilities.
Put in one value, set a timer, replace it with another value when the time runs out.
Add another Command Button so the User can decide when to replace the value.
Perform some math on the set and place the result in the selected cell.
Concatenate all the values in the set and place a String of words, separated by commas, spaces, or other in the cell.Getting two Range selections From one Macro?

I'm not aware of any method other than a Modeless UserForm, so I'm leaving this note for any real experts in VBA. If nobody chimes in, UserForms are really simple.

To get the set of values in a usable format:


Sub SamT_Selections()
Dim ValueSet As Range

Set ValueSet = Selection 'When assigning a variable to an Object, you must use the Set Keyword.End Sub


'To put values in a cell
Sub SamT_Placement2()
Dim PasteCel As Range



PasteCel.Text = SomeThing 'The use of the Text Property forces numbers to be entered as text.


PasteCell.Value = Something ''Value lets the entree be entered as numbers or text.
PasteCel = Something 'Same as using Value



PasteCell = SomeOtherCell 'Same As Copy, PasteAll, values, formates, formulsa, etc with mouse



SomeOtherCell.Copy Destination:=PasteCell 'Same as above
SomeOtherCell.Copy PasteCell 'Same as Destination:= above



SomeOtherCell.Copy
PasteCell.PasteSpecial(parameters) 'Same as Paste Special, select one method of pasting with mouse. Can be repeated to paste more than one parameter.


End Sub

:beerchug:

[Paul_Hossler]

SamT makes excellent points

I'm not clear about what it is you're trying to accomplish, but possibly this might stir some thoughts that you can build on.

1. Select the destination cell to write to
2. Select the range of cells to use
3. Write each cell from #2 to #1


Option Explicit
'pauses processing
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub SelectRange()
Dim bDone As Boolean
Dim rDisplayCell As Range, rSelected As Range


Set rDisplayCell = Nothing
On Error Resume Next
Set rDisplayCell = Application.InputBox("Select the cell to display in, and [OK], or just [Cancel]", _
"Demo", Range("A1").Address, , , , , 8)
On Error GoTo 0
If rDisplayCell Is Nothing Then Exit Sub


bDone = False

Do While Not bDone
Set rSelected = Nothing
On Error Resume Next
Set rSelected = Application.InputBox("Select the range, and [OK], or just [Cancel]", "Demo", , , , , , 8)
On Error GoTo 0

If rSelected Is Nothing Then
bDone = True
Else
Call LowerSubToDoSomething(rDisplayCell, rSelected)
End If
Loop
End Sub

Sub LowerSubToDoSomething(DisplayHere As Range, CellsToDisplay As Range)
Dim rCell As Range

MsgBox "You selected " & CellsToDisplay.Address & " -- " & CellsToDisplay.Cells.Count & _
" cells to display in cell " & DisplayHere.Cells(1, 1).Address

For Each rCell In CellsToDisplay.Cells
DisplayHere.Cells(1, 1).Value = Format(Time, "hh:mm:ss") & " -- " & rCell.Value
Call Sleep(1000) ' snooze for 1 second
Next
End Sub


In the lower sub, you could put the CellsToDisplay into an array

Paul

[Sock]

This has all helped IMMENSELY. I'm sorry for the later response to this, I wanted to get through my code (and work) before posting a response. You guys are awesome! I'll mark this thread as solved.

Thank you so much, everyone.

[snb]

Basically you can use:


Sub M_snb()
sn = Application.InputBox("source", "snb", , , , , , 8)
Application.InputBox("destination", "snb", , , , , , 8).Resize(UBound(sn), UBound(sn, 2)) = sn
End Sub